In Access, there is another very useful wizard and that is Find Unmatched Query Wizard. The Find Unmatched Query Wizard creates a query that finds records or rows in one table that have no related records in another table.
As we have already discussed how data joins together in queries, and how most queries are looking for the matches between two or more tables.
- This is the default join in Access, for example, if we design a query with two tables, tblCustomers and tblOrders, and join those two tables by the CustomerIDs, this query will return only the results that match. In other words, the customers who have placed orders.
- There are times when we don’t want to see the matches, for instance, we may not want to see any customer in our database — the customers who have not placed orders as yet.
- This is exactly what the find unmatched query does.
There are many other possible uses for this kind of query as well.
In our database, we can use it to see which authors have not yet written a project or you could use it to see which employees have not yet elected any health benefits. Let us now open your database which contains Customers and Orders table; go to the Create tab and click on the query wizard button.
Select the Find Unmatched Query Wizard and click Ok.
In this scenario, we will look out for those customers who have not placed an order. In the first screen it’s asking which table or query contains the records you want in the query results.
We now want a list of customers from tblCustomers. Select that option and click Next.
In the following screen, you need to specify which table or query contains the related records. In other words, what table are you using to compare with the first one. For this, we need to find the ones that have not placed orders. We need to select the table that contains information on all orders — tblOrders. Now, click Next.
In the following screen, you need to specify which piece of information is in both tables.
- This will typically be some kind of primary key, foreign key, field, or relationship.
- If you have an existing relationship in your database, Access will select and match those fields for you.
- But, if you have other fields that you can join together, contain similar information, you can choose that here as well.
Here, we have CustID selected by default in both Fields in ‘tblCustomers’ and Fields in ‘tblOrders’. Now, click Next.
In the following screen, you can pick and choose the fields you want to see displayed in the query results.
Let us now select all the available fields and click on the double-headed arrow. This moves all the available fields over to the selected fields area. Now, click Next.
The last screen will allow you to choose a name for your query and click Finish.
Here we have one customer listed as that customer who has not placed an order with us yet.
You can also see how that query was created. For this, you need to go back to the Design View.
This wizard has created an Outer Join between tblCustomer and tblOrders and the Is Null criteria is added to the CustID from tblORders. This is to exclude certain records. In this case, it is the customers who have placed orders, or who have related information in tblOrders.