Aggregate Queries in Microsoft Access Databases
In Microsoft Access, you might want to perform operations on not just one single record, but on a group of records. You can simply create fields that perform operations per row or on every record. But what happens when you perform calculations on a group of records? This is where Aggregate queries come in handy.
Aggregate Query An Aggregate query lets you carry out calculations on record groups rather than perform individual operations, and because of that, it is also referred to as Summary query. It considers the total, subset, or gross amount of records.
In performing calculations on a group of records, there are numerous operations you can follow. Some of these operations are explained below. • Sum – One of the most familiar and simplest ways of doing operations on records is using the Sum function. This adds (or sums) all the values contained in the field. • Average – If you need to calculate the mean, you can use the Average function. This calculates the average value for all values in the field. • Min – This is used in finding the lowest value in all field. • Max – Contrary to Min, this function is used in finding the highest value from the given field. • Count – This returns the total number of records in a field. • StDev – The standard deviation function is used to evaluate a population sample represented as a set of values in a specified field on a query. • Var – This function returns the estimate of the variance for a population sample denoted as a set of values in a designated field on a query.
Here’s a brief explanation on how you can apply the calculation process on a group of records. 1. Open your Access database. 2. On the Create tab, select Query Design.
3. The Show Table dialog box will display. To add the table or tables that you want to use, double-click the table name. Once you’re done adding the tables, click Close. The tables will be displayed in the query grid. 4. Double-click the fields that you want to include. Each field will be shown in a cell in the design grid. 5. Click Totals in the Show/Hide group on the Design tab.
6. Another row will be added just below the Table: row in the design grid.
7. The Total: row allows you to choose which operation to use in every field you selected. If you click the button, all choices will be displayed.
8. Select Sum then run the query. 9. The fields you grouped will now be added up, hencegiving you outputs for the aggregate query you used.
Contact us Ben Beitler ben@accessdatabasetutorial.com (+44) 7881 502400 United Kingdom London
https://www.accessdatabasetutorial.com/