The Excel® Analyst's Guide to Access®

Chapter 3. Beyond Select Queries

Retrieving and displaying specific records with a select query is indeed a fundamental task in analyzing data. However, it's just a small portion of what makes up data analysis. The scope of data analysis is broad and includes grouping and comparing data; updating and deleting data; performing calculations on data; and shaping and reporting data. Access has built in tools and functionality designed specifically to handle each one of these tasks.

In this chapter, you take an in-depth look at the various tools available to you in Access and how they can help you go beyond Select queries.

Aggregate Queries

An aggregate query, sometimes referred to as a group-by query, is a type of query you can build to help you quickly group and summarize your data. With a select query, you can only retrieve records as they appear in your data source. However, with an aggregate query, you can retrieve a summary snapshot of your data that will show you totals, averages, counts, and more.

Creating an Aggregate Query

To get a firm understanding of what an aggregate query does, take the following scenario as an example. You have just been asked to provide the sum of total revenue by period. In response to this request, start a query in Design view and bring in the Period and LineTotal fields, as shown in Figure 3-1. If you run this query as is, you will get every record in your dataset instead of the summary you need.

Figure 3.1. Running this query returns all the records in ...