Table & Relationship design help please

[JBEvergreen]

I need help from anyone who is good at setting up tables and relationships!
My data is a bit convoluted.

Here is what I am trying to do:
I have a list of addresses & accounts. Some accounts are sent to multiple
addresses, some addresses receive multiple accounts. Some contacts on the
address list have two addresses (mailing & legal). How can I set up my tables
and relationships so that I can do a mail merge following this format:

Contact Name
Address

Account Number
Account specific data

Does this make sense?
All help appreciated - Jean

 

[Heathersgoinnuts]

Well first off I would have two different Address tables for each account
Then the first relationship would be the Account table and address 1 table
the second would be the account table and address 2 table
you could call address 1 (mailing) and address 2 (legal)
then you would be able to relate each account with each of the addresses you
enter
ok i am not sure on this but you could try it and see what happens

 

[John Vinson]

I need help from anyone who is good at setting up tables and relationships!
My data is a bit convoluted.

Here is what I am trying to do:
I have a list of addresses & accounts. Some accounts are sent to multiple
addresses, some addresses receive multiple accounts. Some contacts on the
address list have two addresses (mailing & legal). How can I set up my tables
and relationships so that I can do a mail merge following this format:

Contact Name
Address

Account Number
Account specific data

Does this make sense?
All help appreciated - Jean

Whenever you have a many to many relationship you need a *third table*
to model the relationship itself. I'd see the following tables:

Accounts
AccountID
<information about the account>

Contacts
ContactID
LastName
FirstName
<other bio info>

Addresses
AddressID
Address1
Address2
City
StateOrProvince
PostCode

AccountContacts
AccountID <<< link to Accounts 1:m
ContactID <<< link to Contacts 1:m
<<< these two fields are a joint Primary Key
<any info about this contact's role in this account, i.e. "she pays
the bills", "he's the tech geek">

ContactAddresses
ContactID <<< link to Accounts
AddressID <<< link to Addresses
AddressType (e.g. shipping, billing, ...)

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps