Append queries

[kgrauds2]

I have an inventory database where every item is assigned to a certain room. The main table is where all the details about items are stored. Then there's another - archive - table. I want it to be possible that when I change item's assigned room it appends the record with the old value to archive table and keeps the record with the new value in the main table. What's the best way to set this up?

[June7]

With a bunch of VBA code.

If you want history of assignments then have an Assignments table. Items are assigned to rooms like books are assigned to people in a library. You might get ideas from MS Access Lending Library database template.