Unanswered: Best Design for Multiple Tables and Forms
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.
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.
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.
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.
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.