Form deisgn for related tables

[jwleonard]

This will be a definate newbie question but here it goes.... I am new to Access and fighting my way through building a database to learn. I have broken my tables down to as normalized as I am willing to go, mabey a little further than needed. So now I have several tables that are related. I need to know how to make a form update several tables at once and prevent the user from having to mess with the autonumbers that are created. Here is a sample of my tables:

All Primary Keys (PK) are autonumbers

tblStaff
StaffID PK
StaffTypeID FK to tblStaff
FirstName
MiddleInitial
LastName
SSN
AddressID FK to tblAddresses

tblStaffTypes
StaffTypeID PK
StaffType

tblAddresses
AddressID PK
AddressTypeID FK to tblAddressTypes
AddressLine1
AddressLine2
CityID FK to tblCities
StateID FK to tblStates
ZipID FK to tblZips

tblAddressTypes
AddressTypeID PK
AddressType

tblCities
CityID PK
City

tblStates
StateID PK
City

tblZips
ZipID PK
Zips

I now know that I may have taken the normalization a little further than needed but I have it done and would like to use it if I can.

I want a form where a user can add a staff member with all of their information on one form (name, ssn, and address) without having to deal with the autonumbers or needing to do it in a certain order to make it work. I also don't want excessive use of combo boxes (state is OK). So far I haven't found a way to do this, please help.

 

[jwleonard]

I see several people have looked at this post, but no replies! So now I must ask is this really that complicated that no one knows how to do it, or is it so simple that no one wants to bother with helping me? Do I need to include more information or clarify my question?

I did reseach on this before post, but I just can't figure it out, please help if you can.

 

[jwleonard]

Sorry if it came accross that way, that was certainly not my intent. What line in my post gave you that impression? I am certainly open to suggestions and help of any kind.

 

[jwleonard]

as normalized as I am willing to go, mabey (sic) a little further than needed.

I was in a hurry typing that and didn't mean it that way! I had posted regarding this database before and was told I was going TOO FAR with normalization??? I was trying to say this is as far as I knew how to take it.
Anyway thanks for helping!

I discovered the root of my problems was I had related tblStaff and tblAddresses incorrectly (as you also pointed out). I do need multiple addresses for one staff member, I have added StaffID to tblAddresses and made it a FK to tblStaff. I also deleted AddressID from tblStaff. This gives me my one staff member to many addresses that I need and has fixed all my troubles that I had posted about.

In regards to the new fields in the city, state, and zip code fields. I wasn't planning on forming relationships between them to avoid the exact problem with zip codes you mentioned. I only seperated them from the address table to keep from having repeated data. Is that a bad approach to this?

As for combo boxes, I had a friend work on the database and everything had combo boxes (really)! I just want combo boxes where it makes sense to have them.

 

[jwleonard]

Thanks for the advice, it is nice to have someone with your experience helping me learn. I think I have everything I need FOR NOW, but I am sure you will see me needing help agian in the near future. Thanks again!