Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1

    Question Unanswered: Best Design for Multiple Tables and Forms

    Hey Everyone,

    So I have an Equipment Table with let's say Name, Owner, Other. The Other would be all of the other various metadata for equipment. My Owner is a FK that has a 1-Many relationship with a Users Table.

    My predicament:
    I want to add fields for "Date Created" and "Created By" where the Created By would reference the User Table, but if I were to do that, I would have two fields that would reference the same table (Owner and Created By) and, because these can be different values, I can see querying not being clean when I want to show the name for both fields.

    I tried making a "creation" field in the main equipment table, a second table that has the creation information and linking them, but when I went to combine both in the equipment userform, I wasn't quite getting it...

    Regardless, what is the best practice for something like this? Is there a trick to table relationships and/or userform design with multiple tables that I am missing? Please let me know if you need me to give more details or explain something in a different way.

    Thanks
    Last edited by pdevito3; 07-09-14 at 16:27.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    There is a trick when you have 2 fields related to the same table. In a query or the relationship window, you add the user table twice. Access will alias the second copy to something like UserTable_1, which you can change. Then you relate one of the fields to the original table and one to the alias.
    Paul

  3. #3
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    I remember doing that at one point in another situation and then something got in the way... I don't remember what it was though. Regardless, I'll give it a go and let you know if I have an issue.

    Thanks

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No problem.
    Paul

  5. #5
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    Note: my SQL skills aren't advanced enough for me to completely leave the Query Builder yet, so beat with me...

    Regardless, this method seems to work, save for two caveats.

    1) I created the relationship and it worked fine (made the second UserTable_1 as expected). I went to build a query for the data and added 2 user tables and my equipment table, but both of the relationships stemmed to the main user table, the second did not go to the UserTable_1. I got around this by deleting the relationship to the main table and manually adding it in the builder. Didn't seem ideal or proper, but it worked.

    2) To properly query (at least with both of the fields showing), any records with null values won't appear. In this case, one filed is required and the other is automated, so it isn't a big deal in this case, but it could in others.

    Any advise?

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Try to edit the joins between the tables by right-clicking or double clicking on the join line. Choose the appropriate "Select all fields from..." option and see if that solves the problem.
    Paul

  7. #7
    Join Date
    Jun 2014
    Posts
    42
    Provided Answers: 1
    No dice. It just adds the new relationship via the dialog boxes the same as if I dragged it. The second table isn't even visible in the pulldown as an option, even thought it's out... Oh well.

    I'm assuming this will be a non-issue when I start doing this all in SQL?

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    No, you should be able to do it in the design window. You added the user table twice there? I'm not sure the query will see the aliased copy from the relationships window, so you may need to repeat the process of adding it twice in a query.
    Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •