Access 2007: The Missing Manual

Chapter 7. Essential Query Tricks

Every Access expert stocks his or her database with a few (or a few dozen) useful queries that simplify day-to-day tasks. In the previous chapter, you learned how to create queries that chew through avalanches of information and present exactly what you need to see. But as Access masters know, there’s much more power lurking just beneath the surface of the query design window.

In this chapter, you’ll delve into some query magic that’s sure to impress your boss, co-workers, and romantic partners. You’ll learn how to carry out calculations in a query and perform summaries that boil columns of numbers down to neat totals. You’ll also learn how to write super-intelligent filter expressions and how to create dynamic queries that ask for information every time you run them. These techniques are indispensable to the repertoire of any true query fanatic.

Calculated Fields

When you started designing tables, you learned that it’s a database crime to add information that’s based on the data in another field or another table. An example of this mistake is creating a Products table that has both a Price and a PriceWithTax field. The fact that the PriceWithTax field is calculated based on the Price field is a problem. Storing both is a redundant waste of space. Even worse, if the tax rate changes, then you’re left with a lot of records to update and the potential for inconsistent information (like a with-tax price that’s lower than a no-tax price). ...