keep old and add new record

[John D]

I posted this on the queries section, but occured programming might be better -

Can you "easily" edit a record in 1 table, save the edit as a new record and
yet keep the old record.

I have an email mailing list. It's primary key is [person_code] +
[email_address]. When people get new addresses I want to add the new address
AND save the eold address. I want to save the old address because I have a
[campaign_results] table that records each person's response (if any) to
different email newsletters - I want to keep the old address in previous
campaign_results records to preserve the accuracy of the previous address.

Any pointers are greatly appreciated. John D

 

[Jeff Boyce]

John

If you know that PersonA responded to a previous offering, what does having
PersonA's email address AS OF THAT POINT IN TIME allow you to do?

Is your requirement more to track persons and their responses or email
addresses that were used?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John D]

If you know that PersonA responded to a previous offering, what does having
PersonA's email address AS OF THAT POINT IN TIME allow you to do?

Is your requirement more to track persons and their responses or email
addresses that were used?

By far the more important requirement is to track people's behavior, not
their email addresses.

As a practical matter - is it necessary to keep old addresses? Not really.
It's the communication/newsletter that counts.

Years - decades ago even - I used "Advanced DB Master" - really dating
myself I fear. It had the option of <control>K(eep both) for edited records,
and I guess I came to expect it as a common provided process.

There are situations where you want to keep old data as well as save new.
Example - when customers move, you probably want to keep their old addresses
if you've shipped stuff there in case some tracking issues come up, and at
the same time save the new address as the current address.

So - don't worry about a "technical" answer - I'll just save the new address
and be done with it. But I have wondered about the "keep old/add new" process
in Access. I'm sure there's a way to use VBasic to do it - but if I need it,
I'll figure it out then.

Thanks - John D

 

[dymondjack]

Sometimes I run into situations where I want to retain old values as well...
generally in purchase orders, quotes, stuff like that. It's not ideal to
make a new record every time someone changes them, but at the same time, if
you send out a quote and the record gets changed later, you still want to be
able to see what quote it was that you sent way back when, even though the
record itself now holds different data.

I don't really want to save the actual report (.pdf in my case) because that
would take a lot more disk space for something that I may never really have
to go back to, but I have come up with a way to store the data.

I've taken to adding a field to tables where I'd like to do this
(fldChangeNo), and every time the record is updated, the change num as
incremented by one.

Then, on the beforeupdate of the form, I'll write the current record to a
text file on the server (I use pipe delimiters | as they are the least likely
to be used by user inputs). I keep one text file for each table, and the
report has the current change number at it's foot.

I've never actually had a situation where I had to go back and parse that
information (hence I don't have functions to do that yet), but its nice to
know that if I ever need to, I can.

There may be an easier way to do something like this, but I like this
because it doesn't take any more resource in the actual db to save this
information.

I don't know if it would be worth it for what you are doing with emails, but
I thought I'd throw it out there anyway.


--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery

 

[John D]

Thanks Jack - sounds like a good approach - I'll keep it in mind.

John D

 

[rbeach]

John,

You may also look into auditing. This would keep a record of what was
changed, with the "From" and "To" information.

Allen Browne has an excellent dissertation on logging record changes at
http://allenbrowne.com/AppAudit.html