Records that don't match

[Guest]

Hi,

I have 2 tables that both contain a book field. The book field contains a
4-digit number.

One tables has 1083 books and the other has 860 books. I need to create a
query that will show me all the books that don't match in either table. So
I would like to see the 223 books that don't match.

As I have it now, I have joined the book fields and it shows me 806 records
that match each other. I would like to see the records that don't match.

Thanks.

 

[John Viescas]

Use the Find Unmatched Query Wizard. The result will look something like:

SELECT Books1.*
FROM Books1 LEFT JOIN Books2
ON Books1.BookID = Books2.BookID
WHERE Books2.BookID IS NULL

You can also work with your existing query. Double-click the join line
between the two tables and select the option to choose "all rows from Books1
and any matching rows from Books2." Include the ID from the second book
table on the query grid and put the following on the Criteria line under it:

Is Null

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)

 

[John Spencer (MVP)]

I would like to point out that this finds all the books in Books1 that are not
in Books2, HOWEVER; it does not find any books in Books2 that are not in Books1.
I think what John Viescas has given you will work for you since your posting
implied that all the books in Books2 exist in Books1 (1083-860=223).

 

[John Viescas]

Actually, you're right John. One table has 1083 rows, the second has 860
rows, and the Inner Join produces 806, so there are clearly rows in each
table that are not in the other. The full solution is:

SELECT Books1.*, "In Books1 but not Books2" As Explanation
FROM Books1 LEFT JOIN Books2
ON Books1.BookID = Books2.BookID
WHERE Books2.BookID IS NULL
UNION ALL
SELECT Books2.*, "In Books2 but not Books1" As Explanation
FROM Books1 RIGHT JOIN Books2
ON Books1.BookID = Books2.BookID
WHERE Books1.BookID IS NULL

--
John Viescas, author
"Building Microsoft Access Applications" (Coming Soon!)
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)