Comparing Two Similar Tables for Unmatched Values in Multiple Columns

[kw127533]

I have two tables with exactly the same column fields and I'd like to compare the values across about 15 columns from TableA to TableB to determine which Primary ID's have values in one table that do not match the second. In other words I'd like to see if columns 2-15 for a Primary ID in TableA match columns 2-15 for the same Primary ID in TableB. All values in the columns to be compared are numeric. I'd like a solution that would return any and all discrepancies. Please let me know if I need to explain further.

[twinnyfo]

Try something like this:

SELECT TableA.PrimaryKey,
       IIf(TableA.Field1<>TableB.Field1,"X","") AS Field1Check,
       IIf(TableA.Field2<>TableB.Field2,"X","") AS Field2Check,
       IIf(TableA.Field3<>TableB.Field3,"X","") AS Field3Check,
       . . .
FROM TableA INNER JOIN TableB
       ON TableA.PrimaryKey = TableB.PrimaryKey;

This should create a grid with an X in every field that differs between tables.

Not the simplest, and probably not the best solution, but just the first thing that popped to mind. This would allow you to check the values of the fields.

[kw127533]

This looks great, however, I'm getting an error "compile error: expected: case". I'm still extremely new to coding and have only been able to edit and tweak other codes. Is there something I'm missing before or after the code you provided? The bold sections are where there are errors. I sincerely appreciate your help!!



SELECT TableA.PrimaryKey,
IIf(TableA.Field1<>TableB.Field1,"X","") AS Field1Check,
IIf(TableA.Field2<>TableB.Field2,"X","") AS Field2Check,
IIf(TableA.Field3<>TableB.Field3,"X","") AS Field3Check
FROM TableA INNER JOIN TableB
ON TableA.PrimaryKey = TableB.PrimaryKey;

[twinnyfo]

This is merely a SQL statement and should not be in a VBA module. This is to buld a query--not VBA code.

[kw127533]

Thanks! I feel a bit silly now but at least I'm learning. I really appreciate your help!!!

[twinnyfo]

Glad I could help. Let us know if you come across any more challenges.

[NeoPa]

Fine SQL Twinnyfo. I would just suggest a minor amendment to ensure Null values are handled :

SELECT TableA.PrimaryKey,
       IIf(Nz(TableA.Field1,'')<>Nz(TableB.Field1,''),'X','') AS Field1Check,
       IIf(Nz(TableA.Field2,'')<>Nz(TableB.Field2,''),'X','') AS Field2Check,
       IIf(Nz(TableA.Field3,'')<>Nz(TableB.Field3,''),'X','') AS Field3Check,
       ...
FROM   TableA
       INNER JOIN
       TableB
  ON   TableA.PrimaryKey=TableB.PrimaryKey

Clearly, if any of the fields are numerc then the Nz() call can use a 0 instead of the '' empty string.

[twinnyfo]

True, that NeoPa. I seem to forget that all the time in initial coding until it doesn't work because of a null value....

Good catch!

[NeoPa]

LOL!!

Absolutely understand you there Twinnyfo :-D

With more experience comes the dawning of catching it a little earlier in the process. Mainly work here has given me that experience if I'm honest :-)

[kw127533]

Thanks!! My query is running smoothly now! Appreciate all your efforts :)

[twinnyfo]

I'm glad we could be of some help. Hope you have a great weekend!