From the course: Access 2016: Queries

Summarize data with aggregate functions

From the course: Access 2016: Queries

Start my 1-month free trial

Summarize data with aggregate functions

“

- Access databases are really good at collecting and storing large amounts of raw data. However, when it comes time to understanding what all of that data is telling us, it can quickly get overwhelming. Most of the time you'll want to summarize or aggregate data points together to better understand some of the trends or the rankings that might otherwise be buried within the data. Let's take a closer look at how we can build a query using the wizard that will include some of these calculations. For this example let's take a look at the number of products that have been ordered at each state. We'll start this by going to the create tab and coming in here to the Query Wizard. We're going to use the Simple Query Wizard. And the first table that I want to grab is the States Table here. For the States Table I'll grab the State Name, which is the full name of the state, and we'll move that over to the Selected Field side. Now in order to get the number of products that have been ordered in each state, I'm going to have to refer to my Orders Table, and from there I can get the Quantity. This will give me the number of products that have been ordered for each record, and we'll move that over to the Selected Field side, with the State Name. Go ahead and press the Next button, and next we get to define whether we want to see a Detail or a Summary Query. The Detail is just going to show me all of the information out of each of the orders. What I want to do here is get a Summary calculation, so I'll choose this option, and then press the Summary Options button here. Because Quantity is my only numeric field, that's the only one I'm seeing here, that's why I'm not seeing the State Name field, it'll ask me what summary calculations do I want to see based off of our quantity. In this case I want to see all of these. I want to sum up all the quantities so I can see a grand total of how many products have been ordered in each state. I also want to find the Average number of orders, the Minimum number of orders, and the Maximum number of orders. Finally I can also get a count of the number of records in each order, and it'll apply across the entire state. Let's go ahead and say OK to that, and it'll take me back to this screen where I can press next. Finally we get to give our query a name, The States Query is a terrible name, so I'm going to go ahead and change this to Orders-QuantityByState. I leave the option to View the Query Information, and I'll press the Finish button. That'll go ahead and save the query over here into my navigation pane, which is right here, and it'll open it up as well. Let's go ahead and make these columns a little bit wider so I can see all of the data as well as their headers. Let me just double click on each of these lines between them. Now I can see a listing of all of the different states. I can see the total number of products that have been ordered in each state, the average number of products for each of the orders, the lowest number of products that were ordered within that state on a single order, the maximum number of products that were ordered in each state, and finally the number of orders that have been occurring in each state. At any one of these columns I can sort them, I'll just click on this downward pointing arrow and choose to sort largest to smallest. So I can see that the most number of products have gone to the state of New York, followed by 708 products have gone to the state of Texas. Let's go ahead and switch our view back into Design View. I'll click on the Home tab, and then click here to switch this query into Design view where we can see what has just happened. Now the Wizard created these fields here for us. It created the State Name field, and it added this new row that we haven't seen before here called Total. You'll notice that underneath our State Name we've got this line here that says "Grouped By". Essentially what's happening is that Access is taking all the individual records from our orders table, and it's grouping them based off of the orders that have the same state name. So all of the records that say they came from the state of New York are getting grouped together, and all of the records that say they came from the state of Texas are getting grouped together. Now within each of those groups, we're applying some summary calculations. The next column over here is getting the Sum Of Quantity. It is taking the sum value here and it's adding up the number of products that occurred within each of those groups. So for all of the orders that occurred in New York, it's adding up all of those quantities. For all of the products that were ordered in the state of Texas, it's adding up all of those. The next one is finding the Average, and it's using this average calculation. Then we have the Minimum calculation, and the Maximum calculation, and then finally over here at the end, we have this Expression, and if I expand this open, it's applying a Counting function that says Count asterisk, or Count star. Which is just a shortcut way of saying count everything that occurred in each group. You can also see that the Wizard added another table. So we only selected fields from the States table, that would be the State Name, and the Orders table, which would be the Quantity. But Access realized that the only way to get a path from States to Orders is to go through a Customer, so that's how it created this Customers Table here, even though we aren't using any of the fields from the Customer table. The Wizard presents a straight-forward 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 to the full strength of the Aggregate functionality, we need to build them ourselves in Design View.

Contents