Hi all

I wonder if anybody can help with this problem.

I am setting up a basic contact list, with 3 tables - contact, address and company.

What I want to do is be able to add numerous addresses to the company table/form. Then, when I add a contact, I can go to the contact form and select an existing address from a drop down (from the address table), this will then assign this address to the contact, as well as showing this contacts name on company screen.

I have got this working as far as having a drop down of company names on the contacts screen but I can't work out how I can get the address to show on the contact screen when I select a company name from a drop down list?

I have spent ages on this, and its driving me mad, please can somebody hep?

I think i might need to sort out the relationships, but am getting very confused.

Please help!

Thanks

Brad

First, you are going to need a lot more tables to do this correctly. Since you want to add more the one address for each company and each company can have more the one contact (this part I'm assuming) you will need several junction tables, etc.... I would recommend something on the lines of the following:

tblContacts
ContactID - Primary Key - autonumber
ContactLName
ContactFName

tblCompanies
CompanyID - PK - Autonumber
CompanyName
etc...

tblContactsCompanies
ContactCompanyID - PK - Autonumber
ContactID - Foreign Key
CompanyID - FK

tblState
StateID - PK - Autonumber
StateName

tblCities
CityID - PK -AutoNumber
CityName

tblZipCodes
ZipCodeID - PK - AutoNumber
ZipCode
CityID - FK
StateID - FK

tblAddresses
AddressID
StreetAddress
ZipID - FK

tblCompanyAddresses
CompanyAddressID - PK - AutoNumber
CompanyID - FK
AddressID - FK

HTH.

While this structure adheres to the Normal Forms, addresses is one area where I generally opt for convenience for the end user rather than strict adherence to the Forms. It becomes very cumbersome to have to enter an address before you can use that address. As a rule, addresses are usually limited to only a single entity (not necessarily the case when dealing with a single street adress for appartments or suites but the trade off is usually not that significant).

Just my $0.02.

Jerry, I completly agree. In fact, my "standard" address tables are as follows:

tblAddresses
AddressID
Address1
Address2
ZipID

tblZipCodes
ZipCodeID
ZipCode
City
State

I know that to be fully normalized, it should be split down further for cities and states, but convenience makes is much more user friendly. When posting a response here however, I tend to taken the normalization to the nth degree where possible so the Normalization Nazis don't come after me.

LOL, you've taken addresses farther than I would. I tend to just leave them in the table with the entity. But I concur with the zip table.

The only time I break out addresses in a different table is if I have to store multiple addresses for the same person/company, like a mailing address and a shipping address.

Thanks for the info.

Cheers