Entering fadd'l field data into a table based on a lookup table

[Mary McKechnie]

I have a table which uses several lookup fields. I would
like to have additional info from some of the look up
fields automatically fill into the table to show what the
situation was at the time the record was made.

Example:
Lookup has name address and phone number
The name id is attached to a shipping record

6 months later the address changes. I want to keep the
old information ...but as it is all data (on reports ect)
are updated to the new/current information

Apologies for not being able to express this more
effectively...any assistance would be greatly appreciated.

Thanks,

Mary

 

[Tim Ferguson]

Lookup has name address and phone number
The name id is attached to a shipping record

6 months later the address changes. I want to keep the
old information ...but as it is all data (on reports ect)
are updated to the new/current information

Apologies for not being able to express this more
effectively...any assistance would be greatly appreciated.

No: very effectively explained but you do have a design choice.

You decide that all the billing address text is a part of that particular
order, albeit that it pretty often happens to be the same. This may clutter
up the db, but allows you to make one-off changes to the address ("please
deliver it next door because I'm out"). You can implement this quite easily
with a bit of VBA in the AfterUpdate event (or however you choose the
delivery destination).

An alternative is to log each new address: AcmeSupplies1999;
AcmeSupplies2001; AcmeSupplies2001April; and so on. The advantage of this
is that you can still effectively query <"how many deliveries have we made
to this address..">, which the first method may fail on with addresses like
"12 West Street" and "12, West St" and "12 W St" and so on. Is this
important to you?

Hope that helps a bit



Tim F