Microsoft Access 2010 Bible

637

CHAPTER

Advanced Access

Query Techniques

IN THIS CHAPTER

Using calculated fields

Creating queries that calculate

totals

Performing totals on groups of

records

Using different types of

queries that total

Specifying criteria for queries

that total

Creating crosstab queries

Specifying criteria for crosstab

queries

Specifying fixed column

headings for crosstab queries

I

n this chapter, you work with more complex queries in greater detail
than you did in earlier chapters. If you’ve followed through this book in
order from the beginning, you’ve worked with select queries and param-
eters. Chapters 4 and 6 explain relatively simple select queries, in which you
select specific records from one or more tables based on some criteria. You
have not, however, worked with every option that can be used with Access
queries.

As powerful and useful as select queries are, in many situations, you want
queries that perform work on the returned data. As I explain in this chapter,
Access queries are able to perform complex calculations, group data in ways
that are important to users, and find unmatched and missing records.

This chapter covers several advanced ways to build and use Access queries.
I cover totals queries that perform statistical calculations on returned data.
You also find out about crosstab queries that show data in a number of dif-
ferent ways in a single datasheet. This chapter also explains some important
query properties and other options you have when working with Access
queries.

Select queries can specify criteria for single or multiple fields (including cal-
culated fields) using multiple tables. Select queries may also work with wild-
card characters and fields without a value (Is Null). Functions in queries
can specify record criteria or create calculated fields. Finally, Access queries
are a great tool for performing ad-hoc “what-if” scenarios.

This chapter focuses on four specialized types of advanced query techniques:

l Calculated fields: Creating new fields by performing calculations

on the underlying data.