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
FunctionDescription
SumCalculates the sum of all the values for this field in each group. You can specify this function only with number or currency fields.
AvgCalculates 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.
MinReturns 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.
MaxReturns 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.
CountReturns 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.
StDevCalculates 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.
VarCalculates 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.
FirstReturns 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.
LastReturns 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.

[Contents] [Next]

In this tutorial:

  1. Understanding SQL
  2. SQL SELECT Queries
  3. BETWEEN Predicate
  4. Comparison Predicate
  5. EXISTS Predicate
  6. Expression
  7. FROM Clause
  8. GROUP BY Clause
  9. HAVING Clause
  10. IN Clause
  11. IN Predicate
  12. LIKE Predicate
  13. NULL Predicate
  14. PARAMETERS Declaration
  15. Quantified Predicate
  16. Search Condition
  17. SELECT Statement
  18. Subquery
  19. TRANSFORM Statement
  20. UNION Query Operator
  21. WHERE Clause
  22. SQL Action Queries
  23. INSERT Statement (Append Query)
  24. SELECT . . . INTO Statement (Make-Table Query)
  25. UPDATE Statement