Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
Sometimes you will want to include records in your query based upon the results of another query. Often this may include a calculation based upon your data. For instance, instead of just selecting values that are above or below a specific and static number, you may want to select values that are above or below the average of the entire set. In other words, you'll first need to find out what the mathematical average is and then use that value to define the criteria. You can do this by writing an SQL statement right into the field or criteria rows of your query.
So let's construct a query that asks which customers have placed above average orders. Let's go into our Create menu and then Query Design. We'll add our Orders table and our Products table and click Close. So the first thing we need to do is find the average order total across our entire database. We can do that by adding Price to our query, turning on our Totals row, and then changing the Group By to Average.
If I run this query, I see that my average price over the whole database is $29.87. Let's go ahead and back into our Design view and I'll give this an alias to denote what this value is. This is Average Transaction. I'll run the query one more time to make sure we're getting the right results and I can see that we got Average Transaction. Now my Average Transaction reads $29.87. Your average might slightly differ.
Earlier in this chapter, I deleted several records from my database and used them to create an archive database. If you didn't complete those steps, you'll have more records in your current database and your average will be different. Let's go ahead and switch into SQL view so we can see the statement that was constructed in Design view. I'll click on my View menu and I'll choose SQL. Here is the statement that Access has constructed. I'm going to go ahead and copy this statement to the Clipboard and we'll use it later on. So I highlight it and press Ctrl+C to copy it to my Clipboard.
So now that we have an SQL SELECT statement that calculates the average price across our entire database, we can use this as a criteria in another query. Let's go back to our Create menu. We'll do a new query in Design view. This time we want to find out which customers have placed orders that were above the average. The tables I'll choose are DirectCustomers, our Orders table, and the Products table. Go ahead and say Close. Now from our tables, we'll choose a couple fields.
And first I want CustID and FirstName. From my Products table, I'll choose Price. The criteria for Price is anything above the average. So I'll right-click here and say Zoom to bring up my Zoom box. So I'm looking for everything above average. I'll say greater than or equal to and then an open parenthesis. Then I'll paste the SELECT statement that we copied to our Clipboard from the other query. Ctrl+V to paste that in. I'll close the statement with a closing parenthesis.
So now I have an embedded SQL statement right in my Criteria field. We'll go ahead and say OK and I'll run my query. Now in my records, I've got a total of about 168 customers that have placed an order that is higher than the average for my entire data set. As my database grows and more transactions are added to it, the average will move up or down and this query will automatically update to show me all of the customers that are above the current average.
Now that that SQL statement is embedded into this query, I no longer need the first one that I made. I don't even have to save it into my navigation pane. I'll go ahead and just close it, I don't need to save changes, and that statement is permanently saved within this query. The SQL sub-query can be a useful tool in keeping your database clean of extraneous pieces. By combining two queries into a single standalone query, you can eliminate clutter in your navigation pane while reviewing a very specific subset of your data.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 91323 Viewers
80 Video lessons · 138236 Viewers
59 Video lessons · 57067 Viewers
52 Video lessons · 70718 Viewers
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.