Microsoft Access 2010 Bible

Chapter 18: Advanced Access Query Techniques

653

Property Description Select Crosstab Update Delete Make-Table Append

Subdatasheet
Height

Maximum height

of subdatasheet

XX X X X

Subdatasheet
Expanded

Records initially in

their expanded

state?

XX X X

Filter On Load Forms bound to
query have Filter
On Load set by
default

X

Order By On
Load

Forms bound to

query have Order

By On Load set by

default

X

Column
Headings

Fixed-column

headings

X

Use Transaction Run action query
in transaction?

XXX X

Fail on Error Fail operation if
errors occur

XX

Destination
Table

Table name of

destination

XX

Destination DB Name of database X X

Dest Connect
Str

Database

connection string

XX

Working with queries offers many options for how the fields can be displayed and properties for

each specific type of query.

The remainder of this chapter works with advanced options for select queries.

Creating Queries That Calculate Totals

Many times, you want to find information in your tables based on data related to the total of a par-

ticular field or fields. For example, you may want to find the total number of contacts that are both

buyers and sellers or the total amount of money each buyer has spent on products last year. Access

supplies the tools to accomplish these queries without the need for programming.

Access performs totals calculations by using nine aggregate functions that let you determine a spe-

cific value based on the contents of a field. For example, you can determine the average price for

products by type, the maximum and minimum price paid for a product, or the total count of all