Let [Tables] and "Fields"

I have [Contacts] linked to [Companies] by "Company Name".

On the Contacts layout, address fields are in a portal of fields from [Companies] and will auto-populate based on the entry in "Company Name".

HOWEVER - a few of our companies have multiple addresses.

Ideally, a user would select addresses from a drop-down menu that would auto-populate the remaining address fields, but that's where I get a bit lost.

Anyone have a more elegant solution to this, or can someone point me in the right direction on how to do this?

Thanks!

First, I would suggest you link company and contact by companyID rather than company name. If the company gets acquired or decides to change its name in any way all your relationships will be broken. Same goes for all your primary relationships. I tend to use that for all my relationships unless I using the table as a filter. I am sure here we will get varying opinions.

Next, set up an address table and link that to: CompanyTable::CompanyID -> AddressTable::fk_companyID

ContactTable::ContactID -> AddressTable::fk_ContactID

You can now add as many addresses to the comapny and contact as needed. You can use the same approach for Contact Info.

OffPhone

FaxPhone

CellPhone

HomePhone

Emailaddress

HTH

Edited July 10, 200817 yr by Guest

Excuse my late reply, here, but thanks for the help!

Pardon my budding into the topic, but I'm actually having the same issue, I've set up the relationships the same way as you had suggested, but now I'm having trouble figuring out the fields on the contacts layout. What i want is for whomever is inputting the data to be able to select a company and then select from one of the company addresses.

What I'm having trouble with is setting up the fields for the addresses to only display the proper related data. Any suggestions?