Duplicate form - Stamp New Date

[norm]

I am trying to copy a form that holds a vehicle's information, it's current
assignment, etc...
What I need to do is when the vehicle's infor is changed (for example,
assignment) I need to copy to duplicate the date and stamp the date of
"change" but also keeping the history of where the vehicle was previously
assigned.
Any help will be greatly appreciated

 

[Ken Snell \(MVP\)]

My initial impression is that your table structure is not normalized... that
you're storing a separate, complete record for a vehicle for each
assignment. Instead, what you should have is a table that holds just the
assignments for each vehicle, linked back to the main vehicle table by the
vehicle's ID field. Then, you would create a new record in the assignments
table for that vehicle, and you wouldn't need to copy any of the main data
to a new record.

 

[norm]

The problem with this there is more information then just assignments that
has to perserved, I need to duplicate the record:
But first I must mark the original as "Inactive" duplicate the form and
data, make the changes and mark it as "active" this way I have the complete
history of assignments of the specific vehicle. The problem I have is I
inherited this Data Base originally written in DBase IV and that is how it
is set up, so I have 2 tables one "active" and one "assignments" and I need
to perserve the exitsing data. I have a primary key based on VIN number
(Vehicle Indentification Number).


My initial impression is that your table structure is not normalized... that
you're storing a separate, complete record for a vehicle for each
assignment. Instead, what you should have is a table that holds just the
assignments for each vehicle, linked back to the main vehicle table by the
vehicle's ID field. Then, you would create a new record in the assignments
table for that vehicle, and you wouldn't need to copy any of the main data
to a new record.

 

[Linq Adams via AccessMonster.com]

Ken's advice still holds true! You're violating the Cardinal Rule of
Relational Databases by unnecessarily storing duplicate data!

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com

 

[Ken Snell \(MVP\)]

Your unnormalized database structure will continue to cause you major pain
as you find more things that need to updated.

It's not clear from your information where you want to put the "inactive"
record. Is that being put into another table? Or in the same table? If in
the same table, how do you handle duplicate primary key (VIN) values when
you add a new "active" record?

And, how do you want to trigger this "mark the current record inactive and
make a new record as the active one"?