Creating Queries in Access 2010
- 6/15/2010
- Introducing Query Types
- Creating a Query Using the Query Wizard
- Editing a Query in Design View
- Using Criteria to Focus Query Results
- Introducing Operators
- Using Queries to Calculate Values
- Creating a Parameter Query
- Finding Duplicate Records
- Finding Unmatched Records
- Writing Query Results to a New Table
- Creating an Update Query
- Creating a Crosstab Query
- Finding the Largest and Smallest Values in a Field by Using a Query
Finding Unmatched Records
When two tables are in a one-to-many relationship, you can create a Find Unmatched Records query to identify any records in the table on the “one” side that have no corresponding records in the table on the “many” side. For example, in the Northwind sample database, because the Customers and Orders tables are in a one-to-many relationship, you could identify customers that have never placed an order.
Create a Find Unmatched Records Query
Click the Create tab.
Click Query Wizard.
Click Find Unmatched Query Wizard.
Click OK.
Click the table in which you want to find unmatched records.
Click Next.
Click the table or query with related records.
Click Next.
If necessary, click the field in the left pane that is in the table on the “one” side of the relationship.
If necessary, click the field in the right pane that is in the table on the “many” side of the relationship.
Click the Match button to identify the equivalent fields.
Click Next.
Click the name of a field to display in the query results.
Click Add.
Repeat steps 13 and 14 as needed to add fields to the display.
Click Next.
Type a name for the query.
Click Finish.
Try This!
Open the Northwind sample database, click the Create tab, click the Query Wizard button, click Find Unmatched Query Wizard, and then click OK. Click Table: Customers and then click Next. In the next screen, click Table: Orders and then click Next. Verify that ID is highlighted in the Customers table and that CustomerID is highlighted in the Orders table; then click Next. Click Company, click the Add button, and then click Next. In the final screen, click Finish to accept the query name Access suggests. The query you create displays customers that have not placed an order.