Address help urgently needed...

[Guest]

Hi

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

 

[Allen Browne]

Brad, there are many possible approaches.
You need to decide exactly how to relate these tables.

Is it:
a)
- One company has many addresses.
- One contact is identified with just one address, which therefore
identifies him with the company?

OR
b)
- One company has many addresses.
- One company has many contacts.
- One contact may (or may not) be identified with one of his company's
addresses?

OR
c)
- A company has many addresses.
- A company has many contacts, and a contact may be associated with many
companies (either part time, or as they move over the years.)
- Companies and contacts can have multiple addresses.

or some other scenario?

 

[Guest]

Hi

Thank so much for the response.

It is (b), a company has many addresses, with numerouse contacts, each with
a single address, that identifies him/her to the company.

A contact will only ever be associated with one company and, although a
company may have numerous contacts and addresses, each contact has one
address, which connects him/her to the company.

Thanks again for the help!

Kind Regards

Brad

 

[Guest]

Hi

Some more info on this.

I have CompanyID (from Company) linked to CompanyID (from Address) and then
I have AddressID (from Address) linked to AddressID (from Contact).

Is this right. I am not sure which join type they should be though.

Thanks for the subform info.

How do I do it for the Contact form? i.e. so I find a contact on the
Contact form, then click on a company drop down and then, from that, click on
the address drop down (each address at a company has got a description e.g.
Head Office etc).

Thanks so much for your help with this, its invaluable and a real life saver!

Kind Regards

Brad

 

[Allen Browne]

That sounds like you are on the track you want:
- Company table has CompanyID primary key.
- Address table has AddressID primary key, and CompanyID foreign key.
- Contact table has ContactID primary key, and AddressId foreign key.
Therefore a contact belongs to an address that belongs to a company.

The Contact form will have a drop-down for AddressID.
Once you select an address, you have then tied the person to the company
(through the address.) You would not need to tie the person to the company
directly.