Pinpointing differences in tables? (1 Viewer)

Hi,

I have two tables with about 6,000 records each that are *supposed* to be identical. But there are claims that one was tinkered with in the past, and so I've been asked to compare the two tables and see if there are any differences, and if so, what are they. Each table has identical field names.

I've searched this forum and all over the web for how to do this, and what I've come up with is:

-Unmatched Query wizard: When I set up and run this, all it's doing is returning every single record as "unmatched." That doesn't explain what the differences are or really help me at all

-Find Duplicates Query: I thought I could do this and then by process of elimination - the nonduplicates would be "unmatched." However there is a limit of 10 fields to check and I have like 44. Even if it did work it wouldn't tell me the differences in the unmatched fields anyhow.

-Regular Query: Tried to set up a regular design query and put in the Criteria "<>[other table]![same field name]" but then I need to cascade these down under the "Or" so it is stepped and I run out of "Or" rows. This probably wouldn't help much either because what I REALLY need is:

Something that outputs:

The ID number (Primary Key) of the unmatched record, and the value of the unmatched FIELD from each table--which could be multiple unmatched fields for each record.

I found a couple of downloads online for programs that do this but cost $. There must be some way to do it in Access...right?

assuming the record count is the same in both tables, then it depends whether you want to examine single fields (easy) or all fields (hard)

basically to check a single field, just have query that includes both tables, joined on their PK's (ie a 1- 1 join)

then just include the records where tableA.field1 <> tableB.field1

so its easy to check one field.

but if you want to check all 44 fields then you need something like (effectively)

where tableA.field1 <> tableB.field1 or tableA.field2 <> tableB.field2 or tableA.field3 <> tableB.field3 etc

which is a) probably too complex anyway, and b) you will not be able to distinguish what is causing the error

so to check every field at the same time, you will probably need to resort to code, which will be quite a but trickier.

Thanks. Yes, I do need to check every field. As I first posted, I tried to set up a query the way you're saying, but I ran out of vertical rows for "Or"s.

I wouldn't mind doing it in code, but I'm just a beginner as far as VBA and don't know where to start, need some guidance. Otherwise I will have to do the same query 44 seperate times, once for each field.

For a one-off data check like this, it's not worth writing VBA code to iterate all the field comparisons.

Easiest way to proceed is probably:

Create the query, joined on the PK, comparing just one pair of fields.
Switch to SQL view, you should see something like:


Reformat this to look like this (all those braces are unneccessary here):


Then edit the SQL, adding more criteria:


If there are a massive number of rows showing differences, then it might become necessary to attack them with a bit of VBA.

Wow, thanks Atomic Shrimp (love the name! ) I'm plugging it in right now.

Does it matter if I leave the brackets in? Because I think I need to since some of these field names have a space in them (ugh).

So what is this going to show me, if I do it correctly?

Well, I did it, and what I got was the same result as when I ran the Unmatched Query...it returned every single one of the records again (all nearly 6,000).

Is there another way I can do this...maybe in VBA...create an output table that gives me old value vs new value of only the field or fields changed? Even if every record is different - which I don't think is the case - it will at least narrow it down a little for me.

Thanks everyone for your input...

well if its showing every record, its because there is somethnig different in every record

thats the point i was making - if you have too much to compare, you are looking for a needle in a haystack!

try opening the two tables manually, and see if you can see what sort of stuff is different, before you try to use queries - perhaps its just a date stamp, or something you can ignore