MS-Access / Getting Started

---

SELECT Statement

Fetches data from one or more tables or queries to create a logical table (recordset). The items in the select list identify the columns or calculated values to return from the source tables to the new recordset. You identify the tables to be joined in the FROM clause, and you identify the rows to be selected in the WHERE clause. Use GROUP BY to specify how to form groups for an aggregate query, and use HAVING to specify which resulting groups should be included in the result.

Syntax

SELECT [ALL | DISTINCT | DISTINCTROW | TOP number
	[PERCENT]] <select-list>
FROM {table-name [[AS] correlation-name] |
    select-query-name [[AS] correlation-name] |
    (<select-statement>) AS correlation-name |
    <joined table>},...
  [IN <"source database name"> <[source connect
	string]>]
    [WHERE <search-condition>]
    [GROUP BY column-name,...]
    [HAVING <search-condition>]
    [UNION [ALL] <select-statement>]
    [ORDER BY {column-name [ASC | DESC]},...]
    [WITH OWNERACCESS OPTION];

where <select-list> is

{* | {<expression> [AS output-column-name] |
  table-name.* | query-name.* |
  correlation-name.*},...}

and where <joined table> is

({table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
{INNER | {{LEFT | RIGHT | FULL} [OUTER]} JOIN
  {table-name [[AS] correlation-name] |
  select-query-name [[AS] correlation-name] |
  (<select-statement>) AS correlation-name |
  <joined table>}
ON <join-specification>)

Notes: You can supply a correlation name for each table name or query name and use this correlation name as an alias for the full table name when qualifying column names in the <select-list>, in the <join-specification>, or in the WHERE clause and subclauses. If you're joining a table or a query to itself, you must use correlation names to clarify which copy of the table or query you're referring to in the select list, join criteria, or selection criteria. If a table name or a query name is also an SQL reserved word (for example, Order), you must enclose the name in brackets. In SQL Server, you must enclose the name of a table or query that is also an SQL reserved word in brackets or double quotes. If you decide to use quotes, you must also ensure that the server has received the command SET QUOTED_IDENTIFIER ON. Note that when you open a query in an Access project, Access includes the required SET QUOTED_IDENTIFIER ON command in the command string to ensure that any names that you have enclosed in quotes are recognized correctly by SQL Server. However, if you execute an SQL Server query from a desktop database with a pass-through query, you must use brackets or quotes and include this command in the pass-through query.

When you list more than one table or query without join criteria, the source is the Cartesian product of all the tables. For example, FROM TableA, TableB instructs the database to search all the rows of TableA matched with all the rows of TableB. Unless you specify other restricting criteria, the number of logical rows that the database processes could equal the number of rows in TableA times the number of rows in TableB. The database then returns the rows in which the selection criteria specified in the WHERE and HAVING clauses are true.

You can further define which rows the database includes in the output recordset by specifying ALL, DISTINCT, DISTINCTROW (in a desktop database only), TOP n, or TOP n PERCENT.

ALL includes all rows that match the search criteria from the source tables, including potential duplicate rows. DISTINCT requests that the database return only rows that are different from any other row. You cannot update any columns in a query that uses DISTINCT because the database can't identify which of several potentially duplicate rows you intend to update.

DISTINCTROW (the default in Access 7.0-Access 95-and earlier) requests that Access return only rows in which the concatenation of the primary keys from all tables supplying output columns is unique. Depending on the columns you select, you might see rows in the result that contain duplicate values, but each row in the result is derived from a distinct combination of rows in the underlying tables. DISTINCTROW is significant only when you include a join in a query and do not include output columns from all tables. For example, the statement

SELECT tblContacts.WorkStateOrProvince
FROM tblContacts
  INNER JOIN tblContactProducts
  ON tblContacts.ContactID = tblContactProducts.ContactID
WHERE tblContactProducts.DateSold > #7/1/2010#;

returns 92 rows in the ContactsDataCopy.accdb sample database-one row for each product owned by a contact. On the other hand, the statement

SELECT DISTINCTROW tblContacts.WorkStateOrProvince
FROM tblContacts
  INNER JOIN tblContactProducts
  ON tblContacts.ContactID = tblContactProducts.ContactID
WHERE tblContactProducts.DateSold > #7/1/2010#;

returns only 29 rows-one for each distinct row in the tblContacts table, the only table with output columns. The equivalent of the second example in ANSI-standard SQL is as follows:

SELECT tblContacts.WorkStateOrProvince
FROM tblContacts
WHERE tblContacts.ContactID
  IN (Select tblContactProducts.ContactID FROM tblContactProducts
  WHERE tblContactProducts.DateSold > '2010-07-01');

We suspect that Microsoft implemented DISTINCTROW in version 1 because the first release of Access did not support subqueries.

Specify TOP n or TOP n PERCENT to request that the recordset contain only the first n or first n percent of rows. In general, you should specify an ORDER BY clause when you use TOP to indicate the sequence that defines which rows are first, or top. The parameter n must be a positive integer and must be less than or equal to 100 if you include the PERCENT keyword. If you do not include an ORDER BY clause, the sequence of rows returned is undefined. In a TOP query, if the nth and any rows immediately following the nth row are duplicates, the database returns the duplicates; thus, the recordset might have more than n rows. Note that if you specify an order, using TOP does not cause the query to execute any faster; the database must still solve the entire query, order the rows, and return the top rows.

When you include a GROUP BY clause, the select list must be made up of one or more of the SQL aggregate functions or one or more of the column names specified in the GROUP BY clause. A column name in a GROUP BY clause can refer to any column from any table in the FROM clause, even if the column is not named in the select list. If you want to refer to a calculated expression in the GROUP BY clause, you must assign an output column name to the expression in the select list and then refer to that name in the GROUP BY clause. If the GROUP BY clause is preceded by a WHERE clause, the database forms the groups from the rows selected after it applies the WHERE clause.

If you use a HAVING clause but do not include a GROUP BY clause, the select list must be formed using SQL aggregate functions. If you include a GROUP BY clause preceding the HAVING clause, the HAVING search condition applies to each of the groups formed by equal values in the specified columns. If you do not include a GROUP BY clause, the HAVING search condition applies to the entire logical table defined by the SELECT statement.

You use column names or relative output column numbers to specify the columns on whose values the rows returned are ordered. (If you use relative output column numbers, the first output column is 1.) You can specify multiple columns in the ORDER BY clause. When you specify multiple columns, the list is ordered primarily by the first column. If rows exist for which the values of that column are equal, they are ordered by the next column in the ORDER BY list, and so on. When multiple rows contain the matching values in all the columns in the ORDER BY clause, the database can return the matching rows in any order. You can specify ascending (ASC) or descending (DESC) order for each column. If you do not specify ASC or DESC, ASC is assumed. Using an ORDER BY clause in a SELECT statement is the only means of defining the sequence of the returned rows.

In an .mdb-format desktop database that has user-level security implemented, the person running the query not only must have rights to the query but also must have the appropriate rights to the tables used in the query. (These rights include reading data to select rows and updating, inserting, and deleting data using the query.) If your application has multiple users, you might want to secure the tables so that no user has direct access to any of the tables and all users can still run queries defined by you. Assuming you're the owner of both the queries and the tables, you can deny access to the tables but allow access to the queries. To make sure that the queries run properly, you must add the WITH OWNERACCESS OPTION clause to allow users the same access rights as the table owner when accessing the data via the query. Access 2010 does not support user-level security in .accdb-format databases.

If the select-list references a multi-value field, the query returns the individual values separated by commas. A query datasheet provides a combo box that you can use to edit the multiple values. If you bind the column to a combo box control on a form, you can edit the field on the form. To edit the individual values in separate rows, use field-name.Value in your query. For records in the table that have multiple values in the field, the query returns one row per value. The effect is identical to linking to a related many-to-many lookup table using a join. Note, however, that when you ask for field-name.Value from more than one multi-valued column in a table, the resulting query is not updatable because the query returns the Cartesian product of the multiple values in the two fields for each row in the source table.

If the select-list contains an attachment data type, the query datasheet provides an attachment control to allow you to edit the data. You can also edit the data if you bind the field to an Attachment control in a form. You can individually reference one of the three properties of an attachment field: field-name.FileData, field-name.FileName, or field-name. FileType. All three properties return one row per separate attachment for each record in the source table, but you cannot update the values. The FileData property returns the binary attached file, the FileName property returns the original name of the file, and the FileType property returns the file extension.

Examples

To select information about all companies and contacts and any products purchased, enter the following (qxmplAllCompanyContactsAnyProducts):

SELECT tblCompanies.CompanyName, tblContacts.FirstName,
  tblContacts.LastName, CP.ProductName, CP.DateSold, CP.SoldPrice
 FROM ((tblCompanies
  INNER JOIN tblCompanyContacts
  ON tblCompanies.CompanyID = tblCompanyContacts.CompanyID)
  INNER JOIN tblContacts
  ON tblContacts.ContactID = tblCompanyContacts.ContactID)
  LEFT JOIN
   (SELECT tblContactProducts.ContactID, tblProducts.ProductName,
   tblContactProducts.DateSold, tblContactProducts.SoldPrice
  FROM tblProducts
  INNER JOIN tblContactProducts
  ON tblProducts.ProductID = tblContactProducts.ProductID
  WHERE tblProducts.TrialVersion = 0) AS CP
 ON tblContacts.ContactID = CP.ContactID;

Note If you save the previous query in a previous version of Access, when you open the query in Design view, you'll find that Access saves the inner <select-statement> with brackets, like this:

[SELECT tblContactProducts.ContactID, tblProducts.ProductName,
tblContactProducts.DateSold, tblContactProducts.SoldPrice
FROM tblProducts
INNER JOIN tblContactProducts
ON tblProducts.ProductID = tblContactProducts.ProductID
WHERE tblProducts.TrialVersion = 0]. AS CP

This is the internal syntax supported by the JET database engine installed with Access 2003 and earlier. The ACE supplied with Access 2010 no longer modifies the SQL- you'll find the sample query saved exactly as stated in the example without brackets.

To find the average and maximum prices for products by category name, enter the following (qxmplCategoryAvgMaxPrice):

SELECT tblProducts.CategoryDescription,
  Avg(tblProducts.UnitPrice) AS AvgOfUnitPrice,
  Max(tblProducts.UnitPrice) AS MaxOfUnitPrice
 FROM tblProducts
 WHERE tblProducts.TrialVersion = 0
 GROUP BY tblProducts.CategoryDescription;

To find the invoice amounts for all invoices that total more than $150, enter the following (qxmplTotalInvoices>150):

SELECT tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate, Sum(tblContactProducts.SoldPrice) AS InvoiceTotal
 FROM (tblCompanies
  INNER JOIN tblInvoices
  ON tblCompanies.CompanyID = tblInvoices.CompanyID)
  INNER JOIN tblContactProducts
  ON tblInvoices.InvoiceID = tblContactProducts.InvoiceID
 GROUP BY tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate
HAVING Sum(tblContactProducts.SoldPrice) > 150;

To calculate the total for all invoices and list the result for each customer and invoice in descending sequence by order total, enter the following (qxmplOrderTotalSorted):

SELECT TOP 100 PERCENT tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate, Sum(tblContactProducts.SoldPrice) AS InvoiceTotal
 FROM (tblCompanies
  INNER JOIN tblInvoices
  ON tblCompanies.CompanyID = tblInvoices.CompanyID)
  INNER JOIN tblContactProducts
  ON tblInvoices.InvoiceID = tblContactProducts.InvoiceID
 GROUP BY tblCompanies.CompanyName, tblInvoices.InvoiceID,
  tblInvoices.InvoiceDate
 ORDER BY Sum(tblContactProducts.SoldPrice) DESC;

Note: The TOP keyword is optional in a desktop database (.accdb). In SQL Server, you can also specify the calculated column alias name in the ORDER BY clause: ORDER BY InvoiceTotal DESC. In a desktop database, you must repeat the calculation expression as shown in the example.

In a desktop database (.accdb), to create a mailing list for all companies and all contacts, sorted in ascending order by postal code, enter the following (qxmplSortedMailingList):

SELECT tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City,
    tblCompanies.StateOrProvince, tblCompanies.PostalCode
  FROM tblCompanies
UNION
SELECT [FirstName] & " " & ([MiddleInit]+". ") & [LastName] AS Contact,
    tblContacts.HomeAddress, tblContacts.HomeCity,
    tblContacts.HomeStateOrProvince, tblContacts.HomePostalCode
  FROM tblContacts
ORDER BY 5;

Note If you decide to use column names in the ORDER BY clause of a UNION query, the database derives the column names from the names returned by the first query. In this example, you could change the ORDER BY clause to read ORDER BY PostalCode.

To create the same mailing list in a view or in-line function in an SQL Server database, enter the following:

SELECT TOP 100 PERCENT CompanyName, Address, City,
  StateOrProvince, PostalCode
FROM
(SELECT tblCompanies.CompanyName, tblCompanies.Address, tblCompanies.City,
  tblCompanies.StateOrProvince, tblCompanies.PostalCode
 FROM tblCompanies
UNION
SELECT tblContacts.FirstName + ' ' +
  IsNull(tblContacts.MiddleInit + '. ', '') +
  tblContacts.LastName AS Contact,
  tblContacts.HomeAddress, tblContacts.HomeCity,
  tblContacts.HomeStateOrProvince, tblContacts.HomePostalCode
 FROM tblContacts) AS U
ORDER BY 5;

Notice that you must UNION the rows first and then select and sort them all.