Table Design

[Rod Bayron]

Greetings,

We have an Access database that was originally designed
back in Access 2.0 days. It's since been added to and
convert numerous times; today it's an Access 2003 database.

My question has to do with table design and relationships.
We have one table with name, address, city, state, zip
type of information. We have a need to associate multiple
addresses to a name (e.g. home address, business address,
etc.). It seems that we need to move the address
information into its own table and hook address records to
name records.

This database has many reports. The reports are usually
query based and some use the address information.

Is there any way to relate the tables so that we don't
have to change the queries/reports?

Or will we have to recode every query with JOINS?

Or is there a better way to do this?

Any help would be great. Thanks.

 

[Van T. Dinh]

Since you have multiple addresses pername under new arrangement, you will
have to think of *which* address to use in the existing simple single name -
single address arrangement. For example, you may decide to mark one address
(out of many) for each name as the default address and use this for the
existing Reports. In this case, you only need to modify the Queries being
used as the RecordSource of the Reports.

OTOH, you may need to include some or all addresses per name then obviously,
the Reports & their RecordSources need to be changed substantially.