MS Access looks for mismatches

In Access, there is another very useful wizard, and it is a query wizard for finding mismatches. The Find Mismatched Query Wizard creates records or rows that look for records or rows in one table that do not have related records in another table.

As we've discussed how data is connected in queries, and how most queries find matches between two or more tables.

• This is the default connection in Access, for example, if we design a query with two tables (tblCustomers and tblOrders) and connect the two tables through CustomerID, the query will only return matching results. I n other words, the customer who has placed the order.

• Sometimes we don't want to see a match, for example, we may not want to see any customers in our database - customers who haven't placed an order yet.

• This is exactly the query that looks for mismatches.

There are many other possible uses for such queries.

In our database, we can use it to see which authors have not yet written an item, or you can use it to see which employees have not selected any health benefits.

Select the query wizard to find mismatches, and then click OK.

In this case, we will pay attention to those customers who have not placed an order. On the first screen, it requires which table or query contains the records you need in the query results.

We now want a list of customers from tblCustomers. Select this option, and then click Next.

On the following screen, you need to specify which table or query contains the relevant records. xt. o do this, we need to find those who have not placed an order. W e need to select a table with all order information - tblOrders. N ow, click Next.

In the following screen, you need to specify which information is shown in both tables.

• This is usually some type of primary, foreign, field, or relationship.
• If you have existing relationships in your database, Access selects and matches these fields for you.
• However, if you have other fields, you can connect together and contain similar information that you can choose here.

Here, we select CustID by default in the fields in "tblCustomers" and "tblOrders". N ow, click Next.

On the following screen, you can select and select the fields to display in the query results.

Now let's select all available fields and click the two-headed arrow. T his moves all available fields to the selected field area. N ow, click Next.

The last screen will allow you to select a name for the query and click Done.

Here, we list a customer who has not placed an order yet.

You can also see how the query was created. To do this, you need to return to the design view.

This wizard creates an outer join between tblCustomer and tblOrders, and the Is Null standard is added to The CustID from tblORders. rs. n this case, it is the customer who placed the order, or the customer who has the relevant information in tblOrders.