Query to find missing records

[frankbutcher]

Hi.

i.e..............

Table A has records 1,2,3,4,5,6,7,8,9,10

Table B has records 1,2,3,4,5,6

How do I create a query that returns values 7,8,9,10 for Table B when I compare Table A and Table B?

What sort of query do I need? I tried the "Unmatched query" but this did not get the result I wanted.

Thanks for reading.....

Frank.

 

[boblarson]

Yes, you would need the Unmatched query wizard and, if you didn't get the results you expected, I would think you didn't select the correct table when asked which table you would find the unmatched records. Also, you would need to let it know which field would be the field to compare.

 

[frankbutcher]

Thanks for the reply.

I stripped out a lot of the fields within the database and tried the unmatched fields wizard, and it worked as you said.

It must be something within the db.

Many Thanks!!

Frank.

 

[grussuk]

Thanks for the reply.

I stripped out a lot of the fields within the database and tried the unmatched fields wizard, and it worked as you said.

It must be something within the db.

Many Thanks!!

Frank.

I'm having the same problem, with a couple of new small test tables it works ok, but using the main tables and comparing what I think are identical fields it just doesn't.
Did you manage to solve it?

 

[tehNellie]

All the "unmatched query" wizard is doing is performing a LEFT JOIN on the two tables and filtering where the table on the right is NULL. If you're getting unexpected results, it can be helpful to manually write the query (either in SQL or design view)yourself in steps.

So you'd basically start with, from SQL view:

SELECT TableA.column, TableB.column
FROM TableA
LEFT JOIN Tableb ON
TableA.column = TableB.Column;

A left join asks for all information from TableA and only includes information from TableB where it matches. So what you should see using the first post as an example is:

TABLEA.column   TableB.column
------------------------------
1              |        1
2              |        2
3              |        3
4              |        4
5              |        5
6              |        6
7              |
8              |
9              |
10             |

We're getting data we don't want (all the matches), but you can see you've got the right columns and the data that you want/expect to see.

To get rid of matching columns, filter TableB only where [column] is NULL (ie there is no match). I've dropped off tableB from the requested results as it won't contain any data.

SELECT TableA.column
FROM TableA
LEFT JOIN Tableb ON
TableA.column = TableB.Column
WHERE tableb.column is NULL;

Should now return:

TABLEA.column   
---------------
7              |
8              |
9              |
10             |

In the query designer, if you relate the two columns:

1)double click the relationship lines and choose

2) "Include ALL information from 'tableA' and only those records from 'tableB' where the joined fields are equal' You're basically doing the same thing as above. (preview as necessary)

3)Add an "is NULL" to the criteria on TableBcolumn and uncheck the 'show' box. You've written your own unmatched query.

Also Access isn't very good by default at showing the difference between NULL (no data) and a 'blank' entry, "" so if you have blank information in both table columns this might be giving you your misleading results, "" will match to "", but NULL wont match to NULL.

 

[grussuk]

Also Access isn't very good by default at showing the difference between NULL (no data) and a 'blank' entry, "" so if you have blank information in both table columns this might be giving you your misleading results, "" will match to "", but NULL wont match to NULL.

Thank you so much for your help. Several thousand records, I found a null and some decimal points which didn't show up too well.

All is now OK and feel a bit sheepish

 

[tehNellie]

Look on the bright side, you'll know what to look for next time.

Glad to help.