Cloning data to make record history for selected tables

[mba_110]

Hi everyone,

I need your professional guidance to modify my database from the beginning to achieve desired goals in future.

I am working on database for employees which is my access training as well as first project.

I need historic transactions in database for employees like (Contracts, Insurance, Overtime, Payroll, Increments, Bonus, ID renewals, tax payments, training, etc etc many more) which i will recall in my reports for now these tables i have in my database with (Yes no duplicate) entries in EmpID with all tables, that means it will save only current record with one EmpID at one time in whole table relationships with EmpID.

but some data like overtime, insurance renewals are multiple time done by employer for employee for this requirement and history of this renewals i need some sort of arrangement in my database to store the information.

I need your help to guide me how i can achieve this with single entry at every time to update the current as well as save in historical database but any changes should be reflected in both data.

Only thing i need archive should have multiple entries for single employee in each subject based on their activity and renewals (whatever you going guide) and current tables should have latest information for each employee for which i already setup the data entry forms and tables.

I need some arrangements for archives for all required tables to produce the periodic reports and historical information for each employee in future for my reports.

 

[arnelgp]

consider using DataMacro fir each new,update of a table.

 

[MarkK]

...to update the current as well as save in historical database...

It doesn't make any sense to me to have two destinations for the same data. This sounds like a design flaw in your process. Redesign your data storage so that distinct data is only stored in one place.
hth
Mark

 

[mba_110]

Can you give me example please?

How you going to record versions for employees fields? Which is historic?

 

[MarkK]

Data is the recording of events. Events occur in time. When an event occurs, store its details in one place, in one row, and store the date and time in the same row. Done. There is your history.
Don't duplicate that row. Sure, keep a back-up of your database, but never store two sibling versions of a single event, because if you do, then you always need to resolve the question of which one is the authoritative version.
hth
Mark

 

[MarkK]

How you going to record versions for employees fields?

What data are we talking about here? Versions of what?

 

[mba_110]

If you had read my first post in this conversation you will have good idea about what is the issue what is versions.

Anyways, the issue for example I have tblpassport which holds employees passport details but if any employee's passport have been renewed and certain fields like expiry date renew date etc is change in this scenario where i have to record this employees historic passport details this is just one case I have many tables with insurance, payroll, contracts etc.

Each time when I update I have to ovewright the previous details but I need that old details somewhere to recall in my reports and by this way each employee have many renewals and versions of his passport, this is the issue I am talking about.

Please give me some sort of permanent solution because I cannot allow duplicates in EmpID it's one to many relationship in both tables.

 

[CJ_London]

the solution to this

Each time when I update I have to ovewright the previous details but I need that old details somewhere to recall in my reports and by this way each employee have many renewals and versions of his passport, this is the issue I am talking about.

has been answered

When an event occurs, store its details in one place, in one row, and store the date and time in the same row

your issue with this

cannot allow duplicates in EmpID it's one to many relationship in both tables

is down to table design. Any table that stores history has to use an autonumber PK (or some other uniquePK). So I would expect in your table design to have something like

tblEmployees
EmployeePK
EmployeeNo
EmployeeName
….
….

tblPassports
PassportPK
EmployeeFK
PassportNo
DateFrom
DateTo

Or, since passport numbers (should) be unique, that could be the PK

tblPassports
PassportNo (PK)
EmployeeFK
DateFrom
DateTo