How to: Retrieve Records Using Access SQL

The most basic and most often used SQL statement is the SELECT statement. SELECT statements are the workhorses of all SQL statements, and they are commonly referred to as select queries. You use the SELECT statement to retrieve data from the database tables, and the results are usually returned in a set of records (or rows) made up of any number of fields (or columns). You must use the FROM clause to designate which table or tables to select from. The basic structure of a SELECT statement is:

  SELECT field list
    FROM table list

To select all fields from a table, use an asterisk (*). For example, the following statement selects all the fields and all the records from the Customers table:

  SELECT *
    FROM tblCustomers

To limit the fields retrieved by the query, simply use the field names instead. For example:

  SELECT [Last Name], Phone
    FROM tblCustomers

To designate a different name for a field in the result set, use the AS keyword to establish an alias for that field.

  SELECT CustomerID AS [Customer Number]
    FROM tblCustomers

Restricting the Result Set

More often than not, you will not want to retrieve all records from a table. You will want only a subset of those records based on some qualifying criteria. To qualify a SELECT statement, you must use a WHERE clause, which will allow you to specify exactly which records you want to retrieve.

  SELECT *
    FROM tblInvoices
    WHERE CustomerID = 1

Note the CustomerID = 1 portion of the WHERE clause. A WHERE clause can contain up to 40 such expressions, and they can be joined with the And or Or logical operators. Using more than one expression allows you to further filter out records in the result set.

  SELECT *
    FROM tblInvoices
    WHERE CustomerID = 1 AND InvoiceDate > #01/01/98#

Note that the date string is enclosed in number signs (#). If you are using a regular string in an expression, you must enclose the string in single quotation marks ('). For example:

  SELECT *
    FROM tblCustomers
    WHERE [Last Name] = 'White'

If you do not know the whole string value, you can use wildcard characters with the Like operator.

  SELECT *
    FROM tblCustomers
    WHERE [Last Name] LIKE 'W*'

There are a number of wildcard characters to choose from, and the following table details what they are and what they can be used for.

Sorting the Result Set

To specify a particular sort order on one or more fields in the result set, use the optional ORDER BY clause. Records can be sorted in either ascending (ASC) or descending (DESC) order; ascending is the default.

Fields referenced in the ORDER BY clause do not have to be part of the SELECT statement's field list, and sorting can be applied to string, numeric, and date/time values. Always place the ORDER BYclause at the end of the SELECT statement.

  SELECT *
    FROM tblCustomers
    ORDER BY [Last Name], [First Name] DESC

You can also use the field numbers (or positions) instead of field names in the ORDER BY clause.

  SELECT *
    FROM tblCustomers
    ORDER BY 2, 3 DESC