Microsoft® Access® 2010 24-Hour Trainer

24

Data Aggregation and Grouping

When we refer to data being aggregated and grouped, we are describing a process of grouping like items, or fields, and summing, counting, averaging, or using any of the aggregate functions available in Access 2010 queries. A simple example of a query using aggregation and grouping is a query designed to count the number of widgets each department sold in the previous week. A more complex example is using a Crosstab query to track the sale of those same widgets by department, but also displaying the sales of the widgets made on each day of the previous week.

The ability to quickly summarize data into meaningful segments is available using the power of Access 2010 queries. In this lesson you are introduced to the concepts needed to design successful aggregation, or Totals, queries.

LESSON SETUP

For this lesson you need Access 2010 and Lesson 24 files from the book's website at www.wrox.com. You should be familiar with creating database objects and working with queries to complete this lesson successfully.

GROUPING DATA IN QUERIES

Two different methods are available to aggregate, or group, data in an Access query. If no aggregate functions are used in the query, and the goal is to return unique records from many possible duplicated values, the DISTINCT keyword will combine all duplicate records into single records. Each field included in the SELECT clause is evaluated and all fields in the records must exactly match the fields in the other records ...