hollyylloh,

I just re-read your initial comment of "When I go in and change the
clients address, I am actually changing it for historical purposed as well,
unintentionally of course, and this is usually acceptable."

Opps, I forgot that when I responded. Ok, if I understand your
requirements, you need to:

1. Maintain a history of customer name and addresses. Each time a change
is made, you need to keep both the new "current" and the old "current" or
historical record. If you were to change a customer's address 5 time, you
would have 1 current record and 5 historical records.

2. Each transaction needs to be forever linked to the customer address
record that was "current" at the time the transaction was created. In this
way, we can alway retrieve the customer address that was in effect at the
time the transaction.

3. Most report will use the original address record when they print name
and address. Some reports / forms will use the most current (for example,
invoices, statements, etc.).


Is this a valid statement of your requirements?

If so, you will need multiple records per customer. The customer will have
two different keys.

The first key would be the unique address key for each record. The second
key would be account number and would have duplicate entries. You would also
have an CurrRcd flag. The CurRcd flag would be set to “Y” on the most
current record and “N” on the old records.

When the user changes the customer’s address, you will:
1. Set the CurrRcd Flag to “N”.
2. Update the current record
3. Copy the current record to a new record.
4. Set the new record’s CurrRcd flag to “Y”
5. Write out the new current record.
6. Make the new “current rcd” the current rcd.

Or something like that.

On you transaction records, you will need to put both the unique address key
and account number key on each transaction record. You will also need to
have to query tables – qryCustAddrTbl and qryCustomerTbl. Both of these
tables would point at the tblCustomer table. The qryCustAddrTbl will
contains ALL customer address records. You would use the Address Key to
access this file. The qryCustomerTbl would only show (via WHERE) those
records where the CurrRcd Flag = “Y” The query would only select those
records CurrRcdFlag = “Y”. You would use the customer account number field
as they key in this file.

The queries would permit all of the data to reside in one physical table,
which makes queries easy.

I’m tired and I’m not sure I’m making sense. When you read this and if does
not make sense, please post the questions and I will try to answer them or
rephrase my answer.

Basically, if you are going to want to access two potentially different
records (orig cust addr and curr cust addr) you are going to need multiple
master records. The individual transaction rcd will point to the original
address rcd (orig addr key) that was inforce at the time of its creation and
at the current customer address rcd.

Dennis