Hide autonumber primary key ID

[jwleonard]

I am new to Access, and have spent the last few weeks reading everything I could get my hands on about creating databases. I am working on a new database for my office and I need some help please.

I am trying to create normalized tables, so I have broken down my tables into the smallest possible field groups and linked the tables via primary and foreign keys using autonumbers and the primary key in parent tables. My question is how do I have my users interface in forms with actual data instead of the ID numbers but have the ID numbers inserted in the field. Here is a sample of my address table to demonstrate my question:

tblAddresses
AddressID Autonumber PK
StaffID Number (FK to tblStaff)
AddressTypeID Number (FK to tblAddressType)
Address1
Address2
CityID Number (FK to tblCities)
StateID Number (FK to tblStates)
ZipID Number (FK to tblZips)

Obviously all of my tables are not shown here but this should show what I am looking for.

So, how can I let the user input/select the actual data from the forms in combo and text boxes but actually input the ID numbers into the underlying tables. If a user is inserting a new record and selects state I want them to be able to input/select CA not the StateID 1.

I don't know why I am having such a hard time with this, I seem to understand the other concepts but this one has me stumped. I found a few ways to do it, but I don't think they are right and I want to learn this the correct way. Any help is greatly appreciated, thanks!

 

[ghudson]

I question why you have a separate table for the AddressType, City, State and Zip. What purpose does that serve? You are creating unnecessary relationships [and work] with the foreign tables. I also suspect that you have done the same thing with your other tables. I suggest one table with the Address data, one table with the Staff data. You would store the Staff ID in the address data. That field allows you to set a relationship between the two tables.

 

[jwleonard]

I read in several of the books and forums that if you have data that is repeated then you should put it in its own table and form a relationship so the data was only in the database once, saving space in the database and ensuring data integrity. The way it is set up each staff member can have a home address (physical), mailing address, and so on... The state, city, and zip are so I don't have repeating data. That is why I designed the tables the way I did, if this is wrong please tell me why! Thanks for the quick reply!

 

[ghudson]

That is correct that you should not have the same data in more than one table. They key word is "normalize" your data.

But you are taking that advice too far by creating a seperate table just for the States and another just for the Zip codes.

I do not have a quick sample to provide but if you search this site deeper you will find some examples where others have posted table and field guidance.

Check this thread out for it has some good advice about normalization and Pat included some sample db's ... Normalising a database

 

[jwleonard]

I am looking at the samples provided in that thread now.

If I go with your suggestion of tblStaff and tblAddresses I will still need an answer to my original question. How can I allow a user to enter a new staff member and address in a form and not have to deal with knowing or finding the StaffID number and putting into tblAddresses StaffID field.

 

[ghudson]

Use a combo box for that field in the form to allow the user to search and select an ID number from that table.

The combo box wizard will help you build it.

 

[jwleonard]

That was one of the ways I tried but I didn't think it was right, I guess it seemed too easy! Thanks for all the great info.