From the course: Access 2019: Queries

Summarize data with aggregate functions

From the course: Access 2019: Queries

Start my 1-month free trial

Summarize data with aggregate functions

“

- [Instructor] Access databases are really good at collecting and storing large amounts of raw data. However, when it comes to understanding what all of that data is telling us, it can quickly get overwhelming. Most of the time, you want to summarize or aggregate your data points together to get a better understanding of some of the trends or the rankings that may be otherwise buried within all of that data. Let's take a closer look at how we can build a query using the Wizard that'll include some of these calculations. We'll start the process by going to the Create tab, and then clicking on the Query Wizard. And I'm going to use the Simple Query Wizard, and press OK. For this example, let's take a look at the number of products that have been ordered in each state. I'm going to go up here to the dropdown menu, and choose the States table, and we'll add in the StateName. Then I'll go back to the Orders table. And we're going to pull out the Quantity field. Once both of those are added over here into the Selected Fields side, go ahead and press the Next button. That'll take us to a screen that asks us, would you like to see a detail or a summary query? The default option is the Detail. It'll show us every field of every record. With this option we'll essentially see a single row for each order that's been placed in our database. We'll see a column for the state name where it went to, and the quantity of how many products were ordered. Instead though, let's take a look at the Summary Options. I'll go ahead and check this box and then press on the button that says Summary Options. This will allow me to group all of the orders that occurred in the same state together. Then within those groups, we'll calculate some values based off of the Quantity fields. For instance, we can find the Sum total of the number of products that were ordered. We can find the Average number of products that were ordered on each order. We can find the Minimum number of products that were ordered in an order, and we can find the Maximum number of products that were ordered in this single order. Finally, we have a checkbox at the bottom, that will count up the number of orders within each state. I'm going to turn on all of those checkboxes and press the OK button. That'll return us back here to this screen. I can go ahead and press the Next button, and we'll give our query a name. I'll call it Orders-QuantitybyState. Then I'll press the Finish button. Then I'll open up the query here, and I'm going to double-click on each of these lines here between the columns here, so you can see all of the data as well as the column headers. And if I take a look at the bottom, I can see that I have a total of 49 records. This corresponds to the 49 states that we have orders sent to. I can see the sum or the total quantity of products that have been sent to each state. I can see the average number of orders per state, the minimum number of products that were sent to a state on a particular order, the maximum number of products that were sent to an individual state on any one order, and finally the total number of orders that have been sent to each state. This allows me to easily rank these. For instance, I can go ahead and sort the Count of Orders from largest to smallest. So I can see that the state with the most number of orders is New York, followed by Texas. Let's go ahead and take this query into Design View by going to the Home tab and clicking on the View button. And we can see what's going on behind the scenes. This is what the Wizard built for us. The first thing to notice is that we actually have three tables involved in this query. We chose to pull information out of the Orders table as well as the States table, but Access was smart enough to know that the only way to get from the Orders table to the States table, is by going through the Customers table. So it added it into our query even though we're not pulling any fields from it. The other thing to notice here, is that we have a new line in the query design grid down below. We have this row here called Total. In this row we can see how our records are being aggregated together. We can see that all of our orders are being grouped together based off of the state name. Then within the state groups we have the Sum of the orders, the Average number of orders, the Min, the Max and we have this expression one here, and if I open this up, we'll see it just has a counting function, so it's counting up the number of orders, that occurred within each state group. So this is what the Wizard built for us. The Wizard presents a straightforward interface for rapidly selecting several aggregate functions that we can apply to our query. But as with most things in Access, the Wizard can only take you so far. To get the full strength of the aggregate functionality, we'll need to build them ourselves in Design View.

Contents