• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 7. Finding, Filtering, and Sorting > Finding Unmatched Records

Finding Unmatched Records

Sometimes you need to know if records in one table have no matching records in a related table. If you're running a business, for instance, it's handy to see which of your sales leads has generated business. It's also important, if not more so, to know which of those leads hasn't produced any business so you can call on those potential clients again. Here's how to create a query to do just that.

To find unmatched records:

Click the New Object drop-down menu and select Query.

Select Find Unmatched Query Wizard and click OK (Figure 7.11).

Figure 7.11. Select the Find Unmatched Query Wizard from the New Query dialog box.

On the first wizard screen, select the table or query that contains the records you want to appear in the search results, and then click Next (Figure 7.12).

Figure 7.12. Select the table containing the values you want to check for matches…

That is, select the table with records that may not have corresponding records in another table. For this example, we'll use our Contacts table.

Select the table or query to compare the first table to and then click Next (Figure 7.13).

Figure 7.13. …and select the table containing the values you want to match with.

In our example, we want to see how many of our friends are non-authors, so we select Authors.

Select the related fields in the two tables and click the <=> button (Figure 7.14).

Figure 7.14. Select the fields that might contain matching values, and then click the <=> button.

In this case the field we want is called LastName in both tables.

Click Next.

Select a field name in the left pane and click the > button to add it to your query results. Click Next (Figure 7.15).

Figure 7.15. Add the fields that you want to appear in your results.

Type a name for your query in the text box at the top of the checkered flag screen and click Finish.

Just in case you're interested, our query turned up 41 (out of 62) of our contacts that aren't authors.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint