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, coworkers, 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 in 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 ...
Get Access 2010: The Missing Manual now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.