This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
I have been saving reports daily of all of our SKU information from our system for the last few weeks.
Most days there are only a few changes, so there are not always going to be fields changing on every item from these reports.
There is a Modify Timestamp which shows the date last modified, but doesn't show the field which was modified.
There are also some times when the Modify Timestamp doesn't get updated even when certain fields are changed (not sure why).
I'd like to find a way to create an audit trail of changes made, based on the reports I have saved.
I've listed a small example of my ideal input/output below.
Lets say I pulled out this report on 11/1/22:
| SKU | MFG PN | Case Weight | Modify Timestamp |
|---|---|---|---|
| 12345 | 222MP | 12 | 1/1/2022 |
| 67890 | 91HR4 | 15 | 11/1/2022 |
| 99999 | 80 | 2/9/2022 |
Then this would show my report from today 11/9/22:
| SKU | MFG PN | Case Weight | Modify Timestamp |
|---|---|---|---|
| 12345 | 222MP | 12 | 1/1/2022 |
| 67890 | 91HR4 | 15.5 | 11/8/2022 |
| 99999 | ABCDE | 80 | 2/9/2022 |
I would like to make a running list of these changes, if there was a Modify Timestamp, use that, otherwise use a separate column I could add of my report date. I'd probably just stack all of my reports into one sheet to start, and put the date I ran the report in a separate column to the right.
| SKU | Field Changed | Old Value | New Value | Date Changed |
|---|---|---|---|---|
| 67890 | Case Weight | 15 | 15.5 | 11/8/2022 |
| 99999 | MFG PN | ABCDE | 11/9/2022 |
Notice how 99999's time stamp stayed 2/9/2022, but because the MFG PN was still different than the previous day, I'd still like to include that as one of the changes.
Would anyone have any tips of how to approach creating something like this?
I do have a small Access database of most of this information, I upload a new report of all of our information each morning just in case of any changes.
I currently just overwrite any existing information in the field, but I don't know the best way to start keeping track of these changes.
Any tips or direction is appreciated, really not sure how to approach a scenario like this.
A family of Microsoft relational database management systems designed for ease of use.
Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.
I keep all fields of new values and row number rather than one field which can avoid too much hard codeing to specify field name.
when you filter with table name,you can see the track.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(89.60000000000001, 51%, 18%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ES%3C/text%3E%3C/svg%3E)
I've written a blog on creating an Audit Trail with Data macros (see below for blog address). The techniques are similar for creating a form based audit trail.
Whether you copy the whole record are just specific fields depends on how many fields might change.
You might like to take a look at ChangedRecordDemo.zip in my public databases folder at:
https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169
If you have trouble downloading a specific file, clicking on the 'Download' button at the top of the page while no files are selected should download a zip file of all files in the folder, from which you should then be able to unpack the relevant file.
This little demo file includes an illustration of a simple audit trail. In this the history of the current record is shown in a subform, with values which are changed in the next record being highlighted.
The key feature of this demo is that it illustrates the use of code which identifies actual changes to data in a record, rather than merely updates. An update can occur without any of the values in a record actually changing.
I would create an audit table identical to the source table.
Then I would create a Data Macro on the source table which copied the entire record, including both changed and unchanged fields and the date AND time of the change, to a new record in the audit table whenever a change is made to any field in the source table.
If you need to compare New and Old values for a given SKU's update history, you can query the audit table for that SKU.
It would be possible to create a more complex query to lay out the changes as you show in the final sample, but that may not be necessary, depending on the purpose of the report.
The number of records in the audit trail would grow more rapidly than in the source table, of course, but that should not be a problem.
I believe I've seen at least one reference to sample code for auditing offered by participants in this forum. You might be served well with a search on prior posts.