Create History of Master Tables with dates

[Ehtizaz]

Hi,

I want to make a payroll software in Access. I want to perform following jobs.

i) Make a monthly copy/history of my Master Tables which contains all the
information regarding employees.

ii) When this copy is made, a given date (by user) user is added to all
the records. so that this history is can be used for future reporting e.g.
annual earnings report, pay slips for the last 12 months. etc.

iii) When copy/history is made, selected field from my Master Table are
reset to zero, e.g. arrears, overtime, deductions fields for a particular
month are refreshed after the history is updated.

iv) modify the history table for changes.

I am using Access 2003.

 

[Klatuu]

Creating a history table is not really that good an idea. It can complicate
your world when trying to do reporting that will include historical and
current data. A better plan is to use a field in your table to indicate the
record is "archived". Then when you want to use current data only, use
queries that filter out archived records. Do the same when you want to work
with only archived records, filter out current records.

If you need to update fields in current records only, then use an update
query filtered to include current records only.