Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In the last movie, we learned some of the types of calculated totals that we could use within our queries. Let's take a look at how we can build a query using the wizard that will include some of these calculations. We'll go up to the Create tab and we'll select Query Wizard. We will create a Simple Query Wizard. So go ahead and say OK. We'll choose a couple of tables. The first table we want is our DirectCustomers table. From that, we'll take our FirstName and LastName. Then we'll choose our Products table. From there, we'll choose Price.
We'll add that to our query. Go ahead and say Next. Now because we included Price, which is a numerical field, Access gives us some summary options. Let's take a look at those. Here Access gives us the ability to choose Sum, Average, Min, or Max. We can also count up the number of records within our database. Let's go ahead and say OK. We'll turn all of those on and we'll say Next. Let's go ahead and save this query as qry_aggregate.
We will open this query to view the information. Let's select Finish and we'll see we have a query that's similar to the one we built in the last movie with FirstName, LastName, the sum total of transactions, the average price of their transactions, the minimum order that they've ever placed, the highest transaction that they've ever placed, and then number of transactions. Let's take a look at this in Design view to see what the wizard did. There are four interesting things that I'd like to point out here. First of all, Access automatically added the Orders table.
We didn't select anything from the Orders table but Access was smart enough to know that in order to go from Direct Customers to Products, we needed that Orders table in between to make that link. The second thing that's interesting is the Totals row populated with all of the aggregate functions that we saw before. Now we know that we can use the drop- down menu to select these when we're building this manually, and we also have ability to choose from a much wider selection of aggregate functions than what's presented in the wizard. For instance in the wizard, there was no way to get standard deviation or variance.
The third thing that's interesting is that Access added generic aliases to the top of our columns. So we have Sum Of Price: and then we're adding the sum of our price. We've got a column that says Average Of Price: and then that's the one that gets the average price. We can go ahead and change these at anytime to be a little bit more specific. For instance, I'll write Average Transaction, and when we run our query, we'll see that that column header is a little bit more specific. Back in the Design view. The last thing that's interesting is how Access handles the counting.
Let me scroll over here to the right. We'll take a look at the fields that provides the count of the records. Let's expand it open so we can see it. Access, instead of using the Total row to choose Count, it decided to use a function for Count. So basically, it's saying Count(*) or count everything. In the Totals row it specify that this is an Expression. So it has Expression to calculate here and it displays that in the field. So that's as far as the wizard will take us.
We could choose from the four most common aggregate options, Sum, Average, Min, and Max, and apply those to any numerical data field. We have the option to count up the number of records in our query and that uses a function for counting. Let's take a closer look at other functions in calculated fields in the next movie.
Get unlimited access to all courses for just $25/month.Become a member
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.
Your file was successfully uploaded.