Duplicate records in Table 1

[dssjon]

I need to duplicate records in a table based on an ID#
so I can expire the first copy and set the new records as current. I'm doing this because we cannot update records directly, we have to expire old records and update them using a duplicate of them.

So I have a 'status' and 'completion date' field where id=1
I need to update the status and date, but I need to make a copy of that record, re-insert into the same table, and set the current flag = true

Any ideas? Thanks

 

[danvlas]

You may use a date flag instead of a 'yes/no' field.
This way, the 'current' records have the flag Null.
Whenever you want to 'duplicate the records, first flag the completion date with current (or any) date, then select those rows and insert them into the same table.

Something like

Update TableName Set FlagFieldName = Date() Where ID = 1 and FlagFieldName Is Null


After that....

Insert Into TableName (Field1, Field2, Field3...)
Select Field1, Field2, Field3... From TableName Where ID = 1 and FlagFieldName = Date()

Be sure NOT to include the FlagFieldName in the append query.

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[dssjon]

It works, thanks!