Confused by Relationships

[Kriski]

I have scoured the help and forum topics before deciding to post.

I have 2 tables in my contact management database: 1) Contacts (where each record is an individual person) and 2) Companies (where each record is a company and their unique address). I have tried to relate the 2 tables, such that when you create a new record in Contacts, you are able to choose a Company from a drop down list, if applicable.

My key fields are as follows:

in Contacts table: New_Company Record Number (number field) and New_Company (text field) (Do I even need the New_Company field here??)

in Companies table: Record Number (number field) and Company_Name (text field)

I related: Contacts table:New_Company Record Number field to Companies table:Record Number field, which worked great on my initial data transfer (moving the companies out of Contacts and into the Companies table). I entered (well, I used a script to enter) the correct/corresponding Company Record Number into the new Contacts:New_Company Record Number field.

But going forward, I want my colleagues to (not have to know the record # and) just choose the company from the drop down list. When I test this, the company name is entered, but the record # is not returned from the Companies table. That is what I think I need.

I tried creating a second relationship:

I related: Contacts table:New_Company to Companies table:Company_Name, but that didn't seem to help.

Did I provide enough info.? any thoughts/assistance would be greatly appreciated

[comment]

See if this helps:

One2Many.fp7.zip

[Kriski]

Bless you, that is working fabulously now.

My next issue is that several of my Companies have multiple addresses (ie. Bank of America has 5 office locations). I have entered each as a separate record in my Companies table and was trying to allow my user to choose which exact address they wanted for the Bank of America employee. Do I need to create a 3rd table that relates Companies to their multiple office locations? Or how can I offer a selection of the different office addresses once "Bank of America" is chosen?

Thank you SO much!

[comment]

A company should have a single, unique, entry in the Companies table. To accommodate multiple addresses, use a child table of Addresses, related to Companies by CompanyID. This will result in a TOG of:

Addresses >- Companies -< Contacts

As you can see, a contact related to a company is also related to all the company's addresses. This allows you to define a value list of address IDs, showing only related values starting from Contacts. Then use this value list to populate an AddressID field in the Contacts table. Define a new relationship between Contacts and another occurrence of Addresses, matching on AddressID.

[Kriski]

I'm so close. I can't tell you how many times I've re-read your post.

In my Contacts table, I am able to view the 5 Bank of America address options from the Addresses table, but when I choose one, the correct AddressID is displayed/entered, but the correct actual address is not dispalyed in the related fields from the Addresses table. It displays the first address I entered, but not the one I chose.

And I did create the 2nd Addresses occurrence.

I greatly appreciate your help.

Oh, also, where should I store/enter addresses for Companies who don't have multiple locations? In the Companies table or the Addresses table?

[comment]

the correct actual address is not dispalyed in the related fields from the Addresses table. It displays the first address I entered, but not the one I chose.

In order to display the details of the selected address, you must use fields from the second occurrence of the Addresses table - the one that is related to Contacts as :

Contacts::AddressID = Addresses 2::AddressID

where should I store/enter addresses for Companies who don't have multiple locations?

In the Addresses table.

[Kriski]

u r a genius. thank you.