MS Access

A Query selects data to work with. The name of a Query is usually prefixed with qry.

A Recordset is a datasheet representation of the data from the desired Table(s) and/or Query(ies) selected by a Query. Recordsets are just like tables in that they can be browsed, filtered, printed, and updated.

The Criteria row in the Design View can be used to pick specific fields. Expressions can be entered enclosed in brackets ( [ ] ) to indicate a Parameter, i.e. an expression to be as criteria during runtime.

The Crosstab row in the Design View can be used to form columns grouping the fields, rows that either group fields or perform Total Functions, and cells that have the values.

Queries come in two kinds:

• Select Query The most common kind of query. It retrieves data from one or more tables and make a recordset. There are restrictions as to the updates possible through a Select Query. EG: You cannot directly update calculated fields but you can edit the fields used by the calculated field.
• Action Query This kind of query can make changes to multiple records in one operation. Action queries can do four types of actions: Delete, Update, Append, and Make-Table.

The Unique Records Property of a Query is equivalent to the SELECT DISTINCTROW statement in SQL and takes two joined tables, and only shows records from the first table. The Unique Values Property of a Query is equivalent to the SELECT DISTINCT statement in SQL and takes one or more tables, and only shows records whose shown field values are never duplicated. Only one of these Properties can be used at a time. To use either Property, its default value of "No" must be converted to "Yes." Both properties only apply to Select, Append, and Make-Table Queries.

Total Functions in Queries

The Totals row in the Design View of Queries can be used to group records and/or perform Total Functions.

Sum

Returns the sum of all the values for this field in each group.

Avg

Returns the arithmetic average of all the values for this field in each group. Null values ignored.

Min

Returns the lowest value of all the values for this field in each group. Null values ignored.

Max

Returns the highest value of all the values for this field in each group. Null values ignored.

Count

Returns the count of the rows for this field in each group. Null values ignored.

Count(*)

Returns the count of the rows for this field in each group. Null values not ignored.

StDev

Returns the statistical standard deviation of all the values for this field in each group. If the group has less than two rows, a Null value is returned.

Var

Returns the statistical variance of all the values for this field in each group. If the group has less than two rows, a Null value is returned.

First

Returns the first value in this field.

Last

Returns the last value in this field.

Page Modified: (Hand noted: 2007-10-12 18:17:36Z) (Auto noted: 2007-11-17 06:45:38Z)