In this chapter, we will be covering how to calculate on a group of records in Access. We have created a field that calculates row by row or record by record to create a line total or subtotal field, but what if we wanted to calculate down by a group of records rather than by individual ones. We can do this by creating what’s known as an Aggregate Query.
Aggregate Query
An aggregate query also known as a totals or summary query is a sum, mass or group particulars. It can be a total or gross amount or a group or subset of records. Aggregate queries can perform a number of operations. Here’s a simple table listing some of the ways to total on a group of records.
| S.No | Aggregate Functions & Description |
|---|---|
| 1. | SumAdds the field values |
| 2. | AvgAverage of the field values |
| 3. | MinLowest (minimum) field value |
| 4. | MaxHighest (maximum) field value |
| 5. | CountCount of the values (records) |
| 6. | StDevStandard deviation of the field values including date/time fields |
| 7. | VarVariance of the field values including date/time |
Let us open your database and go to the Query Design and include the following tables −
Close this dialog box and the following two fields will be displayed as shown in the query grid in the following screenshot.
This is a simple query and we are displaying only two fields — book title and quantity and when we run it we’re seeing every single individual order in our database.
Let us now run this query and you will see the following results.
This is why book titles are repeating. Separate orders have been placed for each of these books here and they were ordered in different quantities. Let us assume we want to see a summary of only that book title that has been listed only once. And then the sum of the quantity listed beside it.
Let us now go to the Design View and in the Design tab, you will see a Sigma symbol. This is your totals button.
Click the sigma symbol which will open another row underneath your field in the table row and from here, you can specify how you are grouping this query.
We will now group by book title and also sum our quantity field.
If we click on group by area and further click on the drop-down menu, all the options will be listed down. In this case, we will choose the option Sum and then run your query.
You can now see each individual book and also all the individual orders displayed beside the book name.
Concatenation in Access
We learned the process of normalization, storing information in separate fields. Sometimes you want to see or view data together like combining the first name and last name fields as a single field. You can display that information together by creating a calculated field that concatenates one or more strings. You can also add other characters like a comma or period that you may want.
- To concatenate in Access, there are two different operators you can use the ampersand (&) and the plus (+) sign.
- The ampersand will combine two strings into one whereas the plus sign will combine two strings and propagate NULL values, for example, if one value is NULL the entire expression evaluates to null.
Example
Let us take a simple example to understand the process of creating a new query using query design. This is going to be a very simple query that is pulling information from our customers’ table.
Let us now add tblCustomers table close that show table dialog box. We will try some concatenation with some simple named fields.
Let us now add the first name and last name fields and run the query.
As you can see, the first name and the last name are separated into two different fields.
We need to display this information together.
Go back to the Design View and in the third field create a new field called full name. Now, add the full name and type the expression that concatenates those two fields together.
Let us run the query and you will see a new calculated field.
It can concatenate the information from these two fields together, but it doesn’t exactly appear the way we want it to. It runs all of that text together because it’s performing the way we have asked it to. We will now have to ask Access to add space in between the information from these two fields.
Let us now go back to the Design View and add another section to this expression.
Add a Space inside quotes and another ampersand. This makes Access take the information from that first name field; add it with a space and then add the information from the last name field at the very end. Now, run the query again and you will see the following results.