Lookup Tables

[corinthianw]

I haven't used Access extensively and I'm currently in the process of building my first database. Someone suggested I use a lookup table to make it easy to complete the forms and keep the database semi-normalized. I was told to put options for multiple fields in one Lookup table with a group id for each field, then build a group lookup table. The person that suggested this has since left and my db is in limbo. Previous to leaving, it was suggested to find a way to have a form use the group lookup table then pull the values from the larger lookup table based on the group id. I am also having probems with my primary key. I have one form filling out multiple tables. I have the persons ID number as the primary key in all the tables. Is there a way to make it so the person inputting the data only has to put the ID number in the form once? Any help would be greatly appreciated. Thanks, Cory.

[NTC]

Access offers a LookUp as a field type in a table. Some pros avoid them because they mask the true relationship between tables (if this point isn't obviously clear do not worry about it...)..but that can be quite helpful. Here is an example; the list of 50 states 2 letter initials NY NJ etc.... put them in a Table StatesAbbrev... a 1 field table - no key. .....then in whichever table you are putting address make a State field and define it as a lookup and the wizard will help point you to the StatesAbbrev table.

If you keep it this simple - and just to streamline putting in text - it works fine.

In regard to your primary key; entering it multiple times in separate tables in one form; what you want to do is designate the form to be bound to one table - - and then insert the other tables into the form using the Subform icon; when you do so it will launch a wizard that will prompt you as to which field is the cross referencing key - - - once that is set up then the key is entered automatically for you whenever you put in any data into the subforms....