Change tracking: comparing two tables for differences

[andreas_udby]

How can I compare two nearly-identical tables to see which records/fields within them are different?

For background: I have built an Access tool that allows me to generate some fairly complex reports at the touch of a button. The tool is fed an Excel spreadsheet that is downloaded monthly and forwarded to me by an IT manager.

The problem is that, occasionally, things such as job titles and business unit names change, and I have to update my Access queries to accommodate them. I don't mind doing this, but the real issue is that I don't know if things have changed until I run the report and see numbers that are way out of alignment.

I'd like to be able to upload this month's spreadsheet and run a query or macro that compares it to last month's and then produces a report (or datasheet form) that tells me which records aren't the same and which fields within those records are different. This would greatly simplify my life, as I wouldn't have to paw through thousands of records looking for the defect.

Any advice on how to approach this would be greatly appreciated. Thanks!

 

[andreas_udby]

Okay, I figured out how to set a query that shows me which fields in each record have changed:

SpecialtyChange: IIf([Table1.specialty]<>[Table2.specialty],[Table2.specialty],"")

However, the query still spits out all the rows (most with blank fields because there are no changes), instead of just the ones that have changed. Any ideas?

 

[dcx693]

You're on the right track. What you need to do is use a variation of your equation as the criteria for either the [Table1.specialty] field or the [Table2.specialty] field.

Where you have the field displaying [Table1.specialty], in the criteria line, put something like this: <>[Table2].[specialty]

 

[IgorB]

How about using the unmatched query from Wizard!!!!!!!!

Igor.

 

[andreas_udby]

Looked into that, but it doesn't seem to do what I want it to do. I'm looking for something that tells me not only which record changed, but what changed about it. I'm still working on this, though, so if you know of a different way to use the Unmatched Query, I'm open to suggestions.

 

[doupis]

How can I compare two nearly-identical tables to see which records/fields within them are different?

For background: I have built an Access tool that allows me to generate some fairly complex reports at the touch of a button. The tool is fed an Excel spreadsheet that is downloaded monthly and forwarded to me by an IT manager.

The problem is that, occasionally, things such as job titles and business unit names change, and I have to update my Access queries to accommodate them. I don't mind doing this, but the real issue is that I don't know if things have changed until I run the report and see numbers that are way out of alignment.

I'd like to be able to upload this month's spreadsheet and run a query or macro that compares it to last month's and then produces a report (or datasheet form) that tells me which records aren't the same and which fields within those records are different. This would greatly simplify my life, as I wouldn't have to paw through thousands of records looking for the defect.

Any advice on how to approach this would be greatly appreciated. Thanks!

I have a similar issue. I need to compare and approve pricing between two tables: component pricing from a Contractor against component pricing from the Supplier. There needs to be a way to only see a list of FG codes that are unapproved where component pricing exists in the supplier tab.

Once that list is generated, run through the list and approve each FG code if all component pricing is equal (with a tolerance).

This sounds similar, so hopefully we can help each other out.

 

[andreas_udby]

Holy thread necromancy, Batman!

Honestly, it's been ten years since I posted that question, and a lot of databases have come and gone. I'm not sure I have that particular file in my possession anymore; I changed jobs twice since then. However, I'll look through a bunch of old stuff I have archived at home and see if I can find a copy of it.