AuditTrail/Imaging

[Grumm]

Hi, I am after some general advice. I have an access application with about 20 related tables that allows the organisation to estimate construction projects and then track the progress of the project over time (weekly).

At the moment the tables do not have any audit trail or historical information. I will try and explain my requirement with an example.

I have a table called ContractActual that has a multiple key. Included in the table are the following fields: -

WeekEnding
% Complete - Brackets
% Complete - Fixings
% Complete - Finishing
% Complete Value (calculated from an estimate and applying the total % complete.

At the moment the rows are UPDATED so the Week Ending changes each week. What I want to do is Image the rows and create new ones for each week (without having to rekey all the information.

Does access have an auto image facility?

Sorry this is not very clear. Ultimately i want to be able to report on progress over time (weekly) on project progress.

Thanks

 

[whiffy]

Hi,

Without going into too much detail I think what you'll need to do is to create another table called something like ContractActualArchive with these fields:

ContractActualArchiveID (autonumber, primary key)
ContractActualID (foreign key linked to ContractActual's primary key)
WeekEnding (presumably in date format?)
Brackets
% Complete - Fixings
% Complete - Finishing
% Complete Value

Then, using an event you could copy the information from ContractActual to ContractActualArchive and then update ContractActual with your new information.

I haven't thought about it much but you'll probably need to use recordsets to copy the info across.

I hope this gives you something to go on.
I'm a bit pushed for time but if you have any problems let me know.

 

[RobPotts]

If you had tables like

TblProject
ProjectName
ProjectNo (Autonumber)

TblProgress
ProjectNo
WeekEnding
% Complete - Brackets
% Complete - Fixings
% Complete - Finishing
% Complete Value

Link the two tables then build a form from TblProject with a subform TblProgress. Then just add a new line to the subform for each new week this would then give you tracebility. If only some of te values will change from week to week you could copy the the info from the last record to the new record.

Hope this helps, let me know
Rob! [Bigcheeks]

 

[Grumm]

Cheers For That - Will give it a go