The Excel® Analyst's Guide to Access®

Chapter 7. Adding Dimension with Subqueries and Domain Aggregate Functions

Often, you will carry out your analyses in layers, each layer of analysis using or building on the previous layer. Building layers into analytical processes is actually very common. For instance, when you build a query using another query as the data source, you are layering your analysis. When you build a query based on a temporary table created by a make-table query, you are also layering your analysis.

All these conventional methods of layering analyses have two things in common.

• They all add a step to your analytical processes. Every query that has to be run in order to feed another query, or every temporary table that has to be created in order to advance your analysis, adds yet another task that must be completed before you get your final results.

• They all require the creation of temporary tables or transitory queries, inundating your database with table and query objects that lead to a confusing analytical process as well as a database that bloats easily.

This is where subqueries and domain aggregate functions can help. Subqueries and domain aggregate functions allow you to build layers into your analysis within one query, eliminating the need for temporary tables or transitory queries.

Note

The topic of subqueries and domain aggregate functions requires an understanding of SQL (Structured Query Language). Most beginning Access users don't have the foundation in SQL. If you fall into this category, press ...