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)); |
| 