Jeremy Hayden
asked on
Need guidance with Access on how to compare old table with new table and spit out unmatched report
ON a weekly cadence I merge multiple tables to build up to a "FINAL" table. I want to run a query/write code to be able to compare new record with old record of that "FINAL" table. I have heard that you can tell Access to write old record to a "TEMP" table and then compare the 2 tables. With the action of comparing I want to be able to see results of what has changed from old record to the new record.
I am assuming there will be the following steps and I am not sure what type of query/code to use for each:
1. Before overwriting old recordset move to TEMP table (maybe the solution is to always write the record to an archive so we have record of it, not sure yet)
2. Compare Old Recordset with New Recordset
3. Spit out report showing what does not match, based on record and field
4. Once comparison is done, then finalize the new recordset as the current FINAL table
Thank you in advance!!
I am assuming there will be the following steps and I am not sure what type of query/code to use for each:
1. Before overwriting old recordset move to TEMP table (maybe the solution is to always write the record to an archive so we have record of it, not sure yet)
2. Compare Old Recordset with New Recordset
3. Spit out report showing what does not match, based on record and field
4. Once comparison is done, then finalize the new recordset as the current FINAL table
Thank you in advance!!
how to compare old table with new table and spit out unmatched report
To compare two tables and identify records in one of the tables that have no corresponding records in the other table. There are several easiest way to find these records is by using the Find Unmatched Query Wizard. After the wizard builds your query, you can modify the query's design to add or remove fields, or to add joins between the two tables (to indicate fields whose values should match). You can also create your own query to find unmatched records, without using the wizard.
Have a look: https://support.office.com/en-us/article/Compare-two-tables-and-find-records-without-matches-cb20ad48-4eba-402a-b20d-eaf10a5d1cb4
Hope this will help you!!!!!!!!!!
ASKER
Bill...Thank you. I am trying to do this in VBA that does all of this with a push of a bottom in a FORM. I already have code written where it builds the output into Excel. I need to build the code to write new vs. old and then compare the 2 tables.
Jason...I am aware of the built in Unmatched Query Wizard and the ability to change the SQL to compare more fields. I am needing help more on the temp table.
Jason...I am aware of the built in Unmatched Query Wizard and the ability to change the SQL to compare more fields. I am needing help more on the temp table.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That said, depending on your application, you could write your own record update routine rather than allowing other parts of the application or queries to update it directly. You could create an "API" function to update a row in a table and pass it the values needed. It could then first read the existing record, write it to a TEMP table, and then update the row with the new data. This feels a bit awkward and bulky for Access though to me.
Would it be possible for you to take a full snapshot of the table right before you run your merge, and then compare the "before" and "after" tables to see what is different, I assume based on unique keys?
~bp