This article explains salient aspects about Aggregate queries in MS Access, what are the operations is offers, and how to make use of them.
In MS Access you might want to perform calculations on not just a single record, but a group of records. You can always create fields that perform calculations row by row or on each record, but what happens when you wish to perform calculations on a group of records? This is where Aggregate queries come into picture. These queries allow you to calculate record groups instead of individual calculations. Since this query makes use of a sum or group particulars, it is also called summary query.
As mentioned already, aggregate query takes into account the total, or subset, or gross amount of records. There are several operations that can be performed using Aggregate queries. For performing calculations on a group of records, there are several operations that can be followed, a few of which are explained below.
- Sum – One of the most common and easiest ways of performing total on records is by using the Sum function. This adds all the values that are there in the fields.
- Average – If just calculating the sum is not enough, and you also need to calculate the mean, you can use the Average option. This calculates the average value for all values in the fields.
- Min – This is used for finding the lowest or the minimum given value from all fields.
- Max – Contrary to the above mentioned operation, this is used for finding the highest or the maximum value from the given fields.
- Count – This gives account of the total number of records, or values in the field.
- StDev – The standard deviation operation can be used for multiple field values, including the fields with date and time data.
- Var – The Variance operation finds the variance in all field values. This can also take into account the date and time field.
Given below is a brief description for how you can implement the calculation process on a group of records.
- Open your Access database, select Query Design.
- Select the tables that you want to perform the calculation on.
- These tables will now appear in the query grid.
- Particular fields of the table will now be visible with every single order or value being displayed.
- To see the sum of values, go to Design View, open design tab, and select the Sigma Symbol. This is the option that will execute the total command for the group records.
- Another row that appears below your row, is the one where you need to decide the grouping pattern for your query.
- Here you can decide which all fields you want to group. If you select the ‘group by area’, and then drop down, all your options will be listed. You can opt for selecting the Sum option, before you run the query.
- The fields you grouped will now be summed up, thus giving you results for the aggregate query you made use of.
For businesses using Access databases, it is very necessary that they have a contingency plan in place to deal with database crashes. The best way forward involves keeping an accdb recovery tool preinstalled in the system to recover data from a database crash within minutes.
Vivian Stevens is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupt SQL Server and excel recovery software products. For more information visit www.datanumen.com