Microsoft Access Office VB VBA Help and Examples

Is Null Queries or No Matching Records Queries

    You have two tables with a common field in both table (lets say it's called CustomerID) you want to list all the records in table1 that have no match in the table2 . Often you would be told to use NOT IN to get the result you required but this type of query can be very slow as access cannot indexed NOT IN statements, instead I'll show you how to use IS NULL to return the records.

Create a new query, and add both the required tables. Drag the related field from one table to the other table field. In this case the customer id and then right click the join and choose properties. Choose show all records from Table1 and only those records from table 2 where the joined fields are equal.

This query will return a record for every record in table one and also list related recrds in table 2

Now drag the CustomerID fields from both table to the fields boxes and in the criteria for table2.customerID type IS NULL. Then save and run the query.

It will now list only the records in table1 that have no matching record in table2.

Heres the SQL

SELECT [Table1].[CustomerID], [Table2].[CustomerID]
FROM [Table1] LEFT JOIN [Table2] ON [Table1].[CustomerID] = [Table2].[CustomerID]
WHERE ((([Table 2].[CustomerID]) Is Null));
The code and application content of this site is copyright of Smiley I.T. and as such reproduction in any form which is for commercial use requires the permission of the Webmaster. Any use of this code for non-commercial use only requires a link or comment back to the original page you took the code from.