Move from one table to two tables

[moishy]

Hello All Experts, Here's my latest question:

I have a table I use to store archived records, the records originate from two tables (they are joined on one of the fields).
This part works.
The SQL I use is:

INSERT INTO tblHistory ( SellerID, Surname, FirstName, Phone, Cell, Fax, Email, SellerAddress, PropertyID, City, Neighborhood, Address, DateAdded, DateUpdated, DateArchived )
SELECT tblSellers.SellerID, tblSellers.Surname, tblSellers.FirstName, tblSellers.Phone, tblSellers.Cell, tblSellers.Fax, tblSellers.Email, tblSellers.Address, tblApartments.PropertyID,  tblApartments.City, tblApartments.Neighborhood, tblApartments.Address, tblApartments.DateAdded, tblApartments.DateUpdated, Date() AS ArchivedDate
FROM tblSellers INNER JOIN tblApartments ON tblSellers.SellerID = tblApartments.SellerID
WHERE (((tblSellers.SellerID) Like [Forms]![frmRecordManagment]![SubDetails]![txtID])) OR (((tblApartments.PropertyID) Like [Forms]![frmRecordManagment]![SubApartments]![txtPropertyID]));

Now my question is how do I reverse the above query, returning the records to their original tables?

 

[pbaldy]

Given that structure, you'd run 2 append queries, one for each target table. Personally I'd probably use the same 2-table structure for the archived records, but then it's highly unlikely I'd archive at all. I'd probably just have a status flag in the appropriate table.

 

[moishy]

Thank you. That is what I was doing, I was just hoping that it could be done using one query...
I considered using a y/n field named Active instead of archiving, but decided against it because the users directly access the table (even though they know they shouldn't).
I decided not to use 2 tables for archiving for 2 reasons:
1. It makes the db more cumbersome (you may disagree!)
2. There are many cases when the client has other active records, so I don't see a point in saving the client records it two tables.

 

[pbaldy]

Well, it's your baby, so up to you. I do disagree though. It seems more cumbersome to have an extra table and move data back and forth than to have a status field. If the appropriate design for the original tables is two tables, why not the same for archive tables? It would seem like it should be one regular and one archive or two and two, not two and one.

Like I said, it's your baby and you're the one ultimately responsible for taking care of it, so do it the way you feel comfortable with.