Finding missing records between two tables

[fambaus]

Hello Gurus and Friends -

My problem query is this:
I have two tables, A and B, B is a clone of A in structure and data.
To start, the data in A and B match perfectly.

I wanted to create a query to find missing records in B when the data was
been changed/updated.

I then delete 20 random records in table B.
I used the Unmatched Query wizard to create a base query.
I added in the additional key fields to the Where clause with the 'Is Null'
criteria.

My query returns zero records.
What is the falw in my approach? Should I use a subquery instead?

Many thanks!
Mike

 

[Michel Walsh]

Did you check for records in tableA without match in tableB or tableB
without match in tableA ?

You should not create additional criteria but additional lines in the join.

In SQL view, that should look like:



SELECT tableA.*
FROM tableA LEFT JOIN tableB
ON tableA.field1 = tableB.field1
AND tableA.field2 = tableB.field2
AND tableA.field3 = tableB.field3
...
AND tableA.fieldN = tableB.fieldN
WHERE tableB.field1 IS NULL



Vanderghast, Access MVP

 

[fambaus]

Hi Michel -
Thank you for the quick reply.
Let me add more specifics to the post to ensure that I am following along
correctly.
I wish to compare the contents from table 2 to table 1 and find missing
records in table 2

The tables are set up as follows:
TABLE1 TABLE2
Company Company
Paygroup Paygroup
Pay_End_Dt Pay_End_Dt
Off_Cycle Off_Cycle
Page_Num Page_Num
Line_Num Line_Num
Paycheck_Nbr Paycheck_Nbr
Empl_Nbr Empl_Nbr
Empl_Rcd Empl_Rcd
Name Name
Deptid Deptid
Empl_Type Empl_Type
Total_Gross Total_Gross
Total_Taxes Total_Taxes
Total_Deductions Total_Deductions
Net_Pay Net_Pay
Check_Dt Check_Dt
Paycheck_Status Paycheck_Status
Paycheck_Option Paycheck_Option
Paycheck_Adjust Paycheck_Adjust
Paycheck_Reprint Paycheck_Reprint
Paycheck_Cashed Paycheck_Cashed
Paycheck_Addr_Optn Paycheck_Addr_Optn
Location Location
Benefit_Rcd_Nbr Benefit_Rcd_Nbr
Business_Unit Business_Unit

The Primary Key fields for both tables are
Company
Paygroup
Pay_End_Dt
Page_Num
Line_Num

Using your example the query would read:
SELECT table1.*
FROM table1 LEFT JOIN table1
ON table1.Company = table2.Company
And TABLE1.Paygroup = TABLE2.Paygroup
And TABLE1.Pay_End_Dt = TABLE2.Pay_End_Dt
And TABLE1.Off_Cycle = TABLE2.Off_Cycle
And TABLE1.Page_Num = TABLE2.Page_Num
And TABLE1.Line_Num = TABLE2.Line_Num
....
...
And TABLE1.Location = TABLE2.Location
And TABLE1.Benefit_Rcd_Nbr = TABLE2. Benefit_Rcd_Nbr
And TABLE1.Business_Unit = TABLE2. Business_Unit
Where table2.Company is Null

Since the PK is made up of several fields I thought I would have to check
for a Null in each of the PK fields.
I think the other mistake that I was making in my query was not joining on
all fields in the tables.

Please let me know if I am on the right track.

I appreciate your time and expertise with my questions.
Take care,
Mike

 

[Michel Walsh]

The pk insures you that a matching record exists (or not), but that would be
based only on the pk field(s) value. If other fields may have been modified
independently, which will, for you, indicate that the two records are THEN
different, then indeed, you have to add those fields too in the join. The
where clause may only need to be tested on one field involved in the ON
clause (as described here), since it will necessary indicates that no match
has been found on the whole sequence of fields involved. (Because, also, an
initial null value would not match anything)


Vanderghast, Access MVP

 

[fambaus]

Hi Michel -

I found that this approach was working off and on.
I had at least two compare queries that did not work at all.
The query was returning all rows from table A (left table).

After looking through this forum I decided to take a closer look at the data.
I didn't mention that the data in the tables was imported into Access via
Excel CSV files.
What I found was quite a few fields in many of the tables were populated
with Null values.
Even though the source of the data, in this case Oracle, did not allow Nulls
in the field a Null was substitute by Access during the Import process.

So now it is on to a data cleansing exercise..

Thank you once again for all of the help.
Mike

 

[Michel Walsh]

If a null is equal to any other thing, including another null, you can
change:

... AND table1.fieldM = table2.fieldM AND ...

into
... AND Nz(table1.fieldM = table2.fieldM, true) AND ...


but sure, the not-null value may be then present in only one of the two
tables.


Otherwise, indeed, a NULL value compared to another value, including another
null, does not return TRUE, so, any null will 'fire' a NO MATCH.



Vanderghast, Access MVP