Keeping old customer address when they move house.

R

Russell

In this Access application that I am creating there is a
requirement to allow a customers current address to be
kept(that bit is done) but they also want to be able to
change the address when a customer moves house and keep a
record of it. In SQL Server I would probably use an update
trigger to do this? How would I do this under Access?
 
D

Douglas J. Steele

Hopefully you're doing your updates through forms. You can put logic in the
form's BeforeUpdate event to simulate a trigger in SQL Server.
 
R

Russell

What logic would I have to enter to control this. would
this have to be done for each form element or can you do it
as a whole?
 
D

Douglas J. Steele

It's difficult to give exact help without knowing how your application is
designed, and I'm not really sure what you mean by "each form element" vs.
"as a whole".

One approach would be to store the addresses in a separate table in order to
represent the 1-many nature of the relationship between Customer and
Customer Address. Add an Effective_DT and Expiry_DT to the Customer Address
table, and use a query to return the current address which would include
something like
WHERE Effective_DT < Date() AND (Expiry_DT > Date() OR Expiry_DT IS NULL)
In the Form's BeforeUpdate event, save the new address values in variables
and undo the change to the current address (so that I don't lose the old
address). Change the Expiry_DT of the current address record, insert a new
record with the new values keyed in by the user and do a requery.

Another approach would be to write the details of what changed to an "Audit"
table, along the lines of what Allen Browne outlines at
http://members.iinet.net.au/~allenbrowne/AppAudit.html
 
G

Guest

The situation that this would be used in is, I would have a
form called "New Address for Existing Customer" that
displays all the fields in the customer table and the user
can then cycle through to the relvant user and then when
they change the displayed address to the new address to
have the first address inserted to an archive table.
Perhaps triggerd by a button lablled save.

This would then allow a later query to pull the details out
of the main table and the archive table by customer ID.
 
L

Larry Linson

As I understood Doug's suggestion, he recommended that you not "archive" the
old address to a different table (arguably, a violation of relational
database design guidelines, but sometimes expeditious), but just to put
start and stop effective dates and create another record in the same table.
Then, you'll use the current date to choose the "active" address out of the
(one) address table.

If you do that, all you'll have to do is set the "stop effective date" for
the current address record from code, and then create the new address record
with today's date (or date and time, if you prefer) in the address table.

If it becomes _necessary_ to archive in a different table, you may well have
enough information that it would also be useful to put that table in a
separate archive _database_.

Larry Linson
Microsoft Access MVP
 
D

Douglas J. Steele

Just curious, Larry. Why do you call it a violation of relational database
design guidelines?

As I indicated at some point in the thread, Customer to Customer Address is
a 1 to many relationship when you take time into account (although I suppose
a case could be made that it's really a many to many relationship: a
customer can have many different addresses over time, and a particular
address could be associated with many different customers over time)
 
L

Larry Linson

Just curious, Larry. Why do you call it
a violation of relational database
design guidelines?
Click to expand...

Because some of the information ("active/inactive status") is inherent in
the name of the tables in which it is stored, or just the existence of the
separate tables for active and archive information.

It isn't all that far removed from the "separate tables for each year's
data" that we sometimes see mentioned. Almost always, the response is "take
a close look at your design".

It's not a point on which I would expend a lot of energy arguing, but I
suspect a real "relational purist" might get more excited than I would.
<GRIN>

Larry Linson
Microsoft Access MVP
 
D

Douglas J. Steele

Larry Linson said:
Because some of the information ("active/inactive status") is inherent in
the name of the tables in which it is stored, or just the existence of the
separate tables for active and archive information.

It isn't all that far removed from the "separate tables for each year's
data" that we sometimes see mentioned. Almost always, the response is "take
a close look at your design".

It's not a point on which I would expend a lot of energy arguing, but I
suspect a real "relational purist" might get more excited than I would.
<GRIN>
Click to expand...

Gotcha. I must have misread: I thought you were saying that my suggestion of
having effective/expiry dates was a violation. Yes, having more than one
table would be wrong in my mind.
 
Top