There are several ways to handle historical data, John.
I think you have these tables:
a) Person (with person_code primary key),
b) Person_Email table (primary key is [person_code] + [email_address])
c) Campaign_Results table (with foreign key of [email_address])
Therefore one person can have many email addresses (table b.) You can use
tables b and c to discover who the person was that received the email.
I'm not sure that's ideal. It is at least theoretically possible that once
an email address is disused, it could be assigned to someone else. The
email_address in b is not guaranteed unique, and so the lookup from c may be
unreliable.
Therefore it seems to me that table c needs person_code as a foreign key as
well. Now you know for sure both who a campaign message was sent to, and
what email address was used on that occasion.
If you only keep track of a single current email address per person, you no
longer need table b. You can record the current email address as a field in
the Person table, and the actual email address used in a particular campaign
is recorded in table c.
If you do need to keep track of multiple current addresses per person, you
still need table b. In this case, you may want to create the relationship
from [person_code] + [email_address] in table c to [person_code] +
[email_address]) in table b, and not have a relationship from c.person_code
directly to a.person_code.
If you really need to keep a full history of email addresses per person over
time in table b, I would suggest it contains fields like this:
- person_code foreign key to table a.
- email_address
- Start_Date Date/Time when this email address was first used
- End_Date Date/Time blank while the address is valid
- Priority Number lower numbers indicate preferred
address.
This lets you know what email addresses were valid on any date, and which is
the preferred address where a person has multiples.
Another possiblity (probably overkill for this case) is to record a log of
all changes to the table. Details in:
Audit Trail - Log changes at the record level
at:
http://allenbrowne.com/AppAudit.html
Now, if you really want to, it is also possible to use the BeforeUpdate
event procedure of the form to do what you asked. If it's not a new record,
keep track of the field values, cancel the event, and undo the form. Then
AddNew to the form's RecordsetClone, and assign the values to the new
record, and set the form's bookmark to the clone set's LastModified bookmark
so you display the new record. Personally, I would try all the other
possibilities suggested above before resorting to this one.
HTH
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
John D said:
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
