Table Question

[Guest]

I had asked about this questions about a month ago but I am still stuck. I
need to create a table for Sites, a table for Personnel at the sites, and an
Address table because some persnonnel will have a different address from the
site.
Here is what i've done
Table sites
Site ID Primary Key, AddressID(Foriegn Key) Site Account etc.
Table Personnel
Personnel ID (primary Key) Site ID(foreign key) Address ID(foreign key) any
other fields
Table Addresses
Address ID (Primary Key autonumber) Street, City etc.
In the relationships window I related site id in the site table to site id
in the personnel table, I related address id in the address table to
addressID in both the personnel and site tables
If the address id is an autonumber how would I know what id number to put in
the address id field in the personnel and site table. Normally you would have
a dropdown so you could choose for example if it was a category field you
would have the category id field as a drop down and have a list of
categories. I don't know why but I am having real difficulty figuring out how
to set up these tables. What would I connect the autonumber address id field
to so that I could choose in the personnel and site tables.
Hope I'm explaining myself. Would appreciate any help. Thanks in advance. I
am using Access 2003.

Best regards,

Dee

 

[Guest]

Bare-bones of tables:

tbl_Personnel
PersonnelID (PK)
SiteID (FK)
AddressID (FK)

tbl_Sites
SiteID (PK)

tbl_Addresses
AddressID (PK)

If you could have someone working at more than one site, or have more than
one address, then:

tbl_Personnel
PersonnelID (PK)

tbl_Sites
SiteID (PK)

tbl_Addresses
AddressID (PK)

tbl_PersonnelSites
PersonnelID (PK)
SiteID (PK)

tbl_PersonnelAddresses
PersonnelID (PK)
AddressID (PK)