Audit Trail and Reporting

[CementCarver]

I have a client who wants to store history for every field in a database and be able to call up previous records based on a date range. Because they compare values from year to year, what would be the best way of accomplishing this task? I've seen macros on the internet that store the old value after the record has gone through a edit session, but how does one program a solution in an orderly fashion, and be able to drill out reports based on contents of a history/audit table?

Any suggestion would be appreciated.

CementCarver

[Dal Jeanis]

I've seen several full descriptions and examples of this - The basic idea is to create a log file, and before updating any field, output a log record of the before and after values, the user name, and so on. Whatever solution you choose will determine the architecture of your entire application, because it will need to be built into pretty much every form, query, module or macro that allows or causes updates.

The key terms to google are MS ACCESS AUDIT LOG. Here's some relevant pages -
http://www.access-programmers.co.uk/...d.php?t=129803
http://allenbrowne.com/AppAudit.html
http://support.microsoft.com/kb/197592
http://www.databasedev.co.uk/audit_trail.html

[CementCarver]

Thanks for your input Dal Jeanis.

CementCarver