Hollyylloh,

Sorry it took me a couple of days to get back to you and I forgot your main
issue of maintaining the old data.

Couple of questions / assumptions:

a. If a name or address field is changed, you will ALWAYS create a
historical address record.


Ok, how about this solution:

Database changes:

1. Add a Client Address or History table. This will be keyed by the
ClientAddressId.
- On the Client Address table, include the ClientId number.
- On the Client Address table, include a field call ActiveFlag. This
field will have a value of either “Y” or “N”.

2. In all tables where this is a customer number, I would also add a
CustAddressId field. In those tables, you would set the ClientAddressId
field equal to the customer’s current CliendAddressId key.

As Steve suggested, you could use a date field to match back to the customer
address record that was in affect at the time, but having a direct key to the
history record is so much simpler.

New Query Table qrytblCurrClientAddr:
Create a new query that looks at the current Client Address table. You will
use all fields and select ActiveFlag = “Y”. This query will show only active
client address records.

You could join the Client Table to the qrytblCurrClientAddr by ClientID.
This will give you a complete Current Client Table using the customer number.

You could then create another query, called qrytblClientAddr, that joins the
Client Address table to the Client Table by ClientID. The primary key to
this record would be the ClientAddressID. This would provide a complete
customer record for each ClientAddrID value.


Description:

When the user changes an address, your software will:

1. Write out the current customer address record and set its ActiveFlag
field to “N”. Keep the old address information on this record.

2, Write a new customer address record with a new ClientAddrId and the new
address information. The ActiveFlag on this record will be set to “Y”.

As you write out transaction record, you will need to post both the ClientID
and ClientAddrID to the transaction record.

Any report that should utilize the original address will need to be modified
to use the qrytblClientAddr table.

Is that acceptable?

Dennis