Track Changes To Tables and Highlight Changes

[foxhound]

Okay, I have three tables: tbl_currentdata and tbl_previousdata and tbl_archivedata. Each table contains the following fields: empno, empname, position, department, location. In the following order, I would like to:

1) do a delete query to automatically delete the information in tbl_previousdata then copy the information from tbl_currentdata into tbl_previousdata

2) automatically import an external file called infodownload.csv that has no column headings but has data in the same column order into tbl_currentdata

3) now, I would like to compare the records in tbl_currentdata with the records in tbl_previousdata. If any of the values change, I would like to show the record in a new table called tbl_changes (some records may be new or some may have been removed or the value of one of the field values may have changed, so I would like to track that also).

4) I would like to create a report to show the data changes from tbl_changes that will highlight the individual field value that is different from before or the complete record if it is new or has been deleted.

Does anyone have any code that could accomplish the above in Access 2000?

 

[foxhound]

Oops...

Forgot 5) I would like to archive these changes to tbl_archivedata before this code is run again.

 

[guybrush]

FoxHound,

Here is what I would consider....

1) the delete query for data in tbl_previousdata is easy. Just
select *, and run a delete query. You should then use the
docmd.openquery command to run the query. The append query is just as simple as the delete query. (if you need the SQL, let me know).

2) Import the .csv file...

DoCmd.TransferText acImportDelim, , "tbl_currentdata", "PATH\infodownload.csv", False

3) To compare the records, you should use an "outer join" query, sometimes called a "left" or "right" join. Acually, a simple way to see how this works, is to use the "find unmatched wizard" on the query wizard list. pick the 2 tables, and step through the wizard.

Once successful, this query will show you all of the records that are different. You could then turn the "find unmatched" query into a make table query, and they would be in their own table. (tbl_changes)

4) To support your desire to "highlight" different changes, I would recommend adding an attribute to the tbl_changes that tracks exactly what has changed. You could come up with a list of possable changes, and update this column, as appropriate. I would think that you would need to run multiple update queries, each looking for the type of change that happened. It would be quite simple to then build a report that uses that field to format each line, highliting the change.

5) To archive this data, simple perform the same thing that you did above in step one, to a new table of your choice.


That is a high level overview of the steps that I would take. Let me know if you want more detailed info on any of the steps above.

 

[fishingmachine]

guybrush,

can you expand on #4

4) To support your desire to "highlight" different changes, I would recommend adding an attribute to the tbl_changes that tracks exactly what has changed. You could come up with a list of possable changes, and update this column, as appropriate. I would think that you would need to run multiple update queries, each looking for the type of change that happened. It would be quite simple to then build a report that uses that field to format each line, highliting the change.

question - how do i come up with the list of possible changes?

 

[blueshark]

fishingmachine,

you could possibly create a new table that wrote an ID and a before and after message. For example

CustomerID | Change | DateTime
46 | "LNAME changed from Smith to Jones" | 1/1/05 12:14:04 AM
46 | "ADDRESS changed from 123 Main St. to 227 Delaware Ave" | 1/1/05 12:15:44 AM

It would then be convenient to call it up in a subform/report.

 

[fishingmachine]

hi blueshark,

i guess i did not ask my question correctly...

what i meant to ask was...how do i systematically know which field changed when my find unmatched query returns a row of data that contains say 10 fields. i joined on all 10 fields to compare them all.

so since this row of data was returned using my find unmatched query, 1 or more of these fields have data that was changed.

is there a way to systematically tell which of the 10 fields have data that changed?

am i being clear?

thanks,
fishingmachine