Aggregate Queries in Microsoft Access Databases

1
Aggregate Queries in Microsoft Access Databases
2
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.  
3

• 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 wa
  ys 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.

4

• Heres a brief explanation on how you
  can apply the calculation process on a group of
  records.
•  
• Open your Access database.
• On the Create tab, select Query Design.
•  

5
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 youre
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. 
6
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, hencegivi
ng you outputs for the aggregate query you used.
7
Contact us
Ben Beitler
ben_at_accessdatabasetutorial.com
(44) 7881
502400
United Kingdom
London
https//www.accessdatabasetutorial.com/