Company and multiple addresses DB design

[deekay]

At the moment I have simple Access DB with 3 tables Companies,
Contacts and Activities.

In the Company table we have stored the address fields
Address
Street
Suburb
City
Code

Company ---Contacts ---Activities (1 ---many relationships)

This works fine for 97% of contacts on the database.
A problem happens when a contact has a different address from the one
in the Companies table.
For example, a contact is based in one city but the head office of the
company is in another.

What is the best way to handle this?

Move the addresses to the contact table so address is per contact?
Move the addresses to a address separate address table and relate to
the company so that company has multiple addresses?
Move the addresses to a address separate address table and relate to
the contact?

Also something to consider from the user perspective.

At the moment interface used is a main form in datasheet view so a
single row would for be.

Company name, Job title, Salutation, Fname, Sname, Address, Street,
Suburb, City, Code...

Where Company and the address fields are in the company table but the
other fields in the contacts fields.
A user uses this main form and to add a new contact would add a new
row by putting the company ref_no into the query into a new row which
would then fill in company info like name and address automatically.

Please advise considering the interface as well as db design.

[Allen Browne]

Answered in microsoft.public.access.tablesdbdesign

[pdm]

Move the addresses to a address separate address table and relate to
the company so that company has multiple addresses?
Move the addresses to a address separate address table and relate to
the contact?

I would make a table for your companies and if a company has more then
one address just put a new record with the same name. You can specify
eg. Company Name Head Office, Company Name Division... Each company
has 1 or many contacts. Open your form starting with the Companies and
add along a subform the related contacts. Address fields and Companies
should belong together.

I discovered also a problem with my design of Companies and Contacts
and perhaps it could also happens to you. I had companies and many
contacts related to the company. But the problem I discoverd was that
some contacts belong to more then one companies. e.g. some contacts
where CEO of more then one company. Here I needed to change the
relationsship with a between table (many to many).

just some ideas,
patrick.

[Susie DBA [MSFT]]

the best way is to use SQL Server

MDB is for lamers and retards

SERIOUSLY


On Apr 22, 5:44 am, deekay <[e-mail]:

At the moment I have simple Access DB with 3 tables Companies,
Contacts and Activities.

In the Company table we have stored the address fields
Address
Street
Suburb

City

Code

Company ---Contacts ---Activities (1 ---many relationships)

This works fine for 97% of contacts on the database.
A problem happens when a contact has a different address from the one
in the Companies table.
For example, a contact is based in one city but the head office of the
company is in another.

What is the best way to handle this?

Move the addresses to the contact table so address is per contact?
Move the addresses to a address separate address table and relate to
the company so that company has multiple addresses?
Move the addresses to a address separate address table and relate to
the contact?

Also something to consider from the user perspective.

At the moment interface used is a main form in datasheet view so a
single row would for be.

Company name, Job title, Salutation, Fname, Sname, Address, Street,
Suburb, City, Code...

Where Company and the address fields are in the company table but the
other fields in the contacts fields.
A user uses this main form and to add a new contact would add a new
row by putting the company ref_no into the query into a new row which
would then fill in company info like name and address automatically.

Please advise considering the interface as well as db design.

[Larry Linson]

"Susie DBA [MSFT]" <su******@hotmail.comwrote

Susie DBA is an alias of a a r o n k e m p f, a troll who continues to make
a fool of himself in public here in the newsgroups. And, as is obvious to
most everyone, despite using the [MSFT] to impersonate one, Susie/a a r o n,
is not a Microsoft employee. Her/his words are intended to hurt Microsoft,
not to help you, so heed them at your own risk.

Larry Linson
Microsoft Access MVP