Viewers: in countries Watching now:
In this course, author Adam Wilbert illustrates how to create and leverage real-world queries and turn raw data into usable information. The course covers setting up queries, performing calculations, using the built-in Access functions to further refine query results, and identifying top performers or areas for improvement based on a range of criteria.
The Find Unmatched query is another query type that we can use to keep our database organized. The Find Unmatched query will look at one table and find all of the records that will have associated data in another table. Let's take a look at how we can use the Find Unmatched query to find all of the states that don't have any customers in them yet. We'll go up to the Create tab and we'll select the Query Wizard. I'll choose the Unmatched Query Wizard and say OK. Now, we need to choose the table that has the information in it that we're looking for.
We're looking for all the states that don't have customers, so I'll choose the States table. Go ahead and say Next. Now, Access asks us which table or query contains the related records. Here we're looking for customers. So I'll select my DirectCustomers table and say Next. Access has correctly identified how those two tables are related. The Abbreviation field in the States table and the States field in the Customers table. We'll go ahead and say Next again. Now, Access ask us which field we want to see in our query results.
We're interested in finding out which states don't have customers so we'll just take state's name. Go ahead and say Next and we'll accept the default name. Press Finish. Access has identified that the state of Arkansas currently doesn't have any customers. So this could be an area where we could focus an advertising campaign. Let's take a look at one more example of the Find Unmatched query. We'll go back to the Query Wizard, Find Unmatched, and OK. This time we'll find out if we have any products that haven't had any orders placed with them yet.
We'll select the Products table and say Next. Which table or query contains the related records? We're looking for the Orders table here. Go ahead and say Next. Again, Access has identified how those two tables are related. We'll say Next. Which fields do you want to see in the query results? Well, I'm interested in which products haven't had orders for them yet, so I want to know the product ID and the product name that hasn't been used yet. We'll go ahead and say Next and again we'll accept this default name and say Finish.
Now, Access has identified the two products that haven't had any orders placed for them yet. We have the two sampler sizes in the extra virgin oil and the pure oil. We can use this information to identify products that we might want to place a promotion on. So those are just two examples of how you might apply your Find Unmatched query.
There are currently no FAQs about Access 2010: Queries in Depth.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.