Access Database 2010--I am creating a database with forms to ease input of data. I am using lookup tables to create dropdown lists of values to choose from, and other values not in the drop down list maybe entered in the table that is referencing this lookup table. The list may change through the years so it will reflect the values chosen most often during that season of time. Question: is there any need to have a primary key field in the lookup table? There is only one field in the table. There is no reason for uniqueness--the records in the lookup table only provide possible choices for storage in a field in a "Client" table that does have unique records. All uniqueness is in the Client table and the values stored in that table's fields. Prefer to chat answers rather than e-mail back and forth if that works for you.
Already Tried: I began by not having any primary keys in my lookup tables. But since 1)I am not yet to the stage of testing the joins with fake and real data and 2) There are many lookup tables to change--I'd rather not change them all if it isn't necessary and if it will complicate future changing of the lookup table values displayed in the forms.
Hi, I can help answer your question.
You don't need a primary key for your lookup field as you describe it.
The primary key comes in handy when you want to change the words in the Lookup field and are linking by primary key. This way all the entries get changed instead of having to change all rows in the table.
Example:Primary key: NoneLookup: My Client Name
If you wanted to change the lookup later on to "My Client Full Name" you can, however, you will then need to change the text in all your client table since it's not linked by primary key. If you had a primary key linked to the lookup, you would just change "My Client Full Name" in one place and it will automatically show the correct data in the client table.
The primary key is usually an auto incrementing number that you don;'t have to worry about.
Thank you, that clarified the "when" to use a primary key.
Experience: Microsoft Office Specialist