MS-Access / Getting Started

---

SQL SELECT Queries

The SELECT statement forms the core of the SQL database language. You use the SELECT statement to select or retrieve rows and columns from database tables. The SELECT statement syntax contains six major clauses: SELECT, FROM, WHERE, GROUP BY, HAVING, and ORDER BY.

In an Access desktop application (.accdb), Access implements three significant extensions to the standard language: TRANSFORM, to allow you to build crosstab queries; IN, to allow you to specify a remote database connection or to specify column names in a crosstab query; and DISTINCTROW in a SELECT statement, to limit the rows returned from the <table list> to rows that have different primary key values in the tables that supply columns in the <field list>. In a previous version format database (.mdb), you can also use WITH OWNERACCESS OPTION in a SELECT statement to design queries in a secured database that can be run by users who are authorized to use the query, including those who have insufficient access rights to the tables referenced in the query.

Note When you save a query that you have written in SQL in your database, Access often examines your SQL command and adds brackets or extra parentheses to make the command easier to parse and compile. In some cases, Access restates complex predicates or changes the ANSI-standard syntax to one it prefers.

Aggregate Functions: AVG, CHECKSUM_AGG, COUNT, MAX, MIN, STDEV, STDEVP, SUM, VAR, and VARP

See Table below.

Total Functions

Function	Description
Sum	Calculates the sum of all the values for this field in each group. You can specify this function only with number or currency fields.
Avg	Calculates the arithmetic average of all the values for this field in each group. You can specify this function only with number or currency fields. Access does not include any Null values in the calculation.
Min	Returns the lowest value found in this field within each group. For numbers, Min returns the smallest value. For text, Min returns the lowest value in collating sequence ("dictionary"¹ order), without regard to case. Access ignores Null values.
Max	Returns the highest value found in this field within each group. For numbers, Max returns the largest value. For text, Max returns the highest value in collating sequence ("dictionary"¹ order), without regard to case. Access ignores Null values.
Count	Returns the count of the rows in which the specified field is not a Null value. You can also enter the special expression COUNT(*) in the Field row to count all rows in each group, regardless of the presence of Null values.
StDev	Calculates the statistical standard deviation of all the values for this field in each group. You can specify this function only with number or currency fields. If the group does not contain at least two rows, Access returns a Null value.
Var	Calculates the statistical variance of all the values for this field in each group. You can specify this function only with number or currency fields. If the group does not contain at least two rows, Access returns a Null value.
First	Returns the value for the field from the first row encountered in the group. Note that the first row might not be the one with the lowest value. It also might not be the row you think is "first" within the group. Because First depends on the actual physical sequence of stored data, it essentially returns an unpredictable value from within the group.
Last	Returns the value for the field from the last row encountered in the group. Note that the last row might not be the one with the highest value. It also might not be the row you think is "last" within the group. Because Last depends on the actual physical sequence of stored data, it essentially returns an unpredictable value from within the group.

¹You can change the sort order for new databases you create by clicking the File tab on the Backstage view, clicking Options, and then using the New Database Sort Order list in the General category. The default value is General-Legacy, which sorts your data according to the language specified for your operating system.