Creating aggregate calculations
Video: Creating aggregate calculationsIn 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.
- Next Steps
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.
- Naming conventions and best practices
- Working with joins
- Using comparison operators
- Defining criteria for select queries
- Creating parameter queries
- Creating calculated fields
- Working with dates and times
- Using the Expression Builder
- Creating conditional statements
- Making, deleting and appending records
- Building reports
Creating aggregate calculations
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.
There are currently no FAQs about Access 2010: Queries in Depth.