Hi all,
Am new to the forum, and need some help fixing a database i created for work. the basic idea is there are a series of tables that are mostly unrelated to help make things a little easier at work. One table tracks calibration dates and breaks it down so at the push of a button it runs a report that shows all items due current/next month/quarter/year, another table tracks status of certain items simply put whether the status is up or down for whatever reason. the two tables i am concerned with are the active and historical tables. so what i am trying to do is in the active table will only be a single record for a specific part number serial number. what i cant get to work is the historical table so every time either a record is added or changed in the active table it copies the only new record or the changes to the historical table to enable a trend analysis of sorts. i tried doing an append query but that took all the active records and appended them to the historical causing duplicates of the same record in the historical table instead of just either the updated record or the new record. that data is input using to forms one for new and one for amendments or updates and upon saving is running an append query from table a to table b but is not doing what i was looking for.for example below this is what is doing.

active table records
a
b
c (change c1)

historical table records
a
a
b
b
c
c1
this is what i want it to do
active table records
a
b
c (change c1)
historical table records
a
b
c
c1