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.
In the Query Wizard we've seen that there are options beyond the simple Select query. One of those is the Find Duplicates query. The Find Duplicates query is a specific query type that will locate duplicate records within your database. This can be used for cleaning up your data tables or reviewing records with multiple entries. We can create one by going to the Create tab and using the Query Wizard. I'll select the Find Duplicates Query Wizard and say OK. The first screen ask us which table or query do you want to search for duplicate fields.
We're going to look at our Orders table. The second screen asks which field might contain duplicate information. Let's identify all of our orders where we had the same customer placed two orders on the same day. We'll choose OrderDate and CustomerID. Let's go ahead and say Next. This window asks, do you want the query to show fields in addition to those with duplicate values? Let's add OrderID so we have a reference point in case we do have duplicate values. I'll go ahead and say Next and I'll accept the default name and say Finish.
Access shows us that we do have duplicate values. I've got two orders placed by the same customer on February 9th, 2005 and an additional two orders placed by a different customer on 11/14/2009. Each order has its own unique OrderID number. Let's go ahead and modify this query a little bit so we have some more information. We'll go to the Home tab and switch back to Design view. Now, let's add our customer name and phone number so that we can call the customer and see if they really intended to place two orders or if it was a mistake.
I'll right-click and say Show Table and we'll add our DirectCustomers table. Now, Access already identified that the CustomerID matches in both tables and creates the join. We'll add FirstName and LastName and phone number to our query. Let's run it again. Now, we can see the customers that placed those duplicate orders. We have an easy way of calling the customer and find out if it's a mistake, or if they did intend to place two orders. So Find Duplicates can be really handy in keeping your database clean and organized.
Sorting out data entry errors that can often arise from importing tables from external sources is another perfect use for Find Duplicates.
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.