I am trying to piece together the best practice for lookups and have read the basics but have some questions on the finer points of setting it up.
I have seen many times:
http://access.mvps.org/access/lookupfields.htm
and I am trying to avoid table-level lookups.
I have table with a list of user initials tblInitials-
ID, Initials, FullName
I want to use this in many tables so the person can record who did the action - for instance I have a table called tblActions -
ID, Action, ActionDate, ActionInitials
and I want the user to be able to select their initials when entering the action on a form.
I have read that the best way for novices is to use the combobox wizard as per the answer here:
https://stackoverflow.com/questions/16379232/look-up-vs-relationship-microsoft-access
The things I am unclear about are:
When creating the Actions table do I just make a field "ActionInitials" and set it to Data Type "Number"? Is there anything else I need to do? Any other options that are recommended for a lookup field that will be used in the combobox wizard?
After running the combobox wizard do I need to create a relationship between the tblInitials!ID and the tblActions!ActionInitials?
If I use the table level lookup this relationship is automatically created but if I use the combobox wizard it is not.
I checked out the example databases here:
https://www.access-programmers.co.uk/forums/showthread.php?t=207989
and they do have the relationships but I am not sure if they have been manually created and if they are necessary? What referential integrity is suggested for this kind of relationship?
I have seen many times:
http://access.mvps.org/access/lookupfields.htm
and I am trying to avoid table-level lookups.
I have table with a list of user initials tblInitials-
ID, Initials, FullName
I want to use this in many tables so the person can record who did the action - for instance I have a table called tblActions -
ID, Action, ActionDate, ActionInitials
and I want the user to be able to select their initials when entering the action on a form.
I have read that the best way for novices is to use the combobox wizard as per the answer here:
https://stackoverflow.com/questions/16379232/look-up-vs-relationship-microsoft-access
The things I am unclear about are:
When creating the Actions table do I just make a field "ActionInitials" and set it to Data Type "Number"? Is there anything else I need to do? Any other options that are recommended for a lookup field that will be used in the combobox wizard?
After running the combobox wizard do I need to create a relationship between the tblInitials!ID and the tblActions!ActionInitials?
If I use the table level lookup this relationship is automatically created but if I use the combobox wizard it is not.
I checked out the example databases here:
https://www.access-programmers.co.uk/forums/showthread.php?t=207989
and they do have the relationships but I am not sure if they have been manually created and if they are necessary? What referential integrity is suggested for this kind of relationship?