MS Access - Unmatched Query Assistant
Ms access tutorial
MS Access - Unmatched Query Wizard
In Access there is another very useful wizard and it is Find Wizard unmatched queries . The Unmatched Query Find wizard creates a query that finds records or rows in one table that do not have related records in another table.
As we have already explained, how data is associated in queries and how most queries are displayed for 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 these two tables by CustomerIDs, this query will only return results that matchlay. In other words, customers who have placed orders.
There are times when we don't want to see matches, for example, we may not want to see any customer in our database - customers who don't have not placed an order yet.
- This is exactly what the unmatched search query does.
There are many other possible uses for this type of request.
In our database, we can use it to see which authors have not yet written a project or you could use to see which employees have not yet chosen benefits from health. Now let's open your database which contains the Customers and Orders tables; go to the Create tab and click on the query wizard buttonyou.
Select Unmatched Search Assistant and click OK .
In this scenario, we will search for customers who have not placed an order. In the first screen, it is asked which table or query contains the desired records in the query results.
We now want a list of customers from tblCustomers . Select this option and click Next .
In the next screen, you need to specify which table or query contains the related records . In other words, which table do you use to compare with the first one. For this we need to find those who have not placed an order. We need to select the table which contains informationtions on all commands - tblOrders . Now click on Next .
In the next screen, you need to specify what information is in the two tables.
- This usually goes some sort of primary key, foreign key, field, or relationship.
- If you have an existing relationship in your database, Access will select and match these fields for you.
- But, if you have other fields that you can attach that have similar information, you can choose it here as well.
Here we have CustID selected by default in both fields of 'tblCustomers ' and fields in 'tblOrders '. Now click on Next .
InOn the next screen, you can choose which fields you want displayed in the query results.
Now select all available fields and click on the double headed arrow. This moves all available fields to the area selected fields . Now click on Next .
The last screen will allow you to choose a name for your query and click on Finish >.
Here we have a customer listed as this customer who has not yet placed an order with us.
You canalso see how this query was created. To do this, you must go back to Design View .
This wizard has created an outer join between tblCustomer and tblOrders and Is Null criteria are added to the CustID from tblORders. This is to exclude certain records. In this case, these are the customers who have placed orders, or have related information in tblOrders.