Office development
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Writing SQL Statements

This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.

When you create a report, you usually want to display data without affecting the data itself. The SQL statement that you construct to retrieve the data will therefore usually be a SELECT statement. The following table describes the common types of SELECT queries.

Query typeDescriptionExample
Simple SELECTSelects specified fields (columns) from one or more tables.
SELECT ProductID, ProductName FROM Products;
TotalsGroups data at table level or at group level; may summarize data with SQL aggregate functions.
SELECT Title, Min(BirthDate) AS MinOfBirthDate FROM Employees GROUP BY Title;
INNER JOINJoins two tables on a field that both have in common; results include only records (rows) for which both tables share a common value in the joined field.
SELECT CategoryName, ProductName FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID ORDER BY CategoryName;
LEFT OUTER JOIN and RIGHT OUTER JOINJoins two tables on a field that both have in common; results include all records from one table and only records from the other table where the joined fields are equal. Useful for finding unmatched records in two tables.
SELECT Employees.LastName, Orders.OrderID FROM Employees LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID ORDER BY Employees.LastName;
UNIONReturns combined result set including records from two tables that have common fields.
SELECT City, CompanyName, ContactName FROM Customers UNION SELECT City, CompanyName, ContactName FROM Suppliers ORDER BY City, CompanyName;
CROSSTABSummarizes tabular data in a columnar format.
TRANSFORM Sum([Order Details].[UnitPrice]*[Quantity]) AS Total SELECT OrderID FROM Products INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID GROUP BY OrderID PIVOT ProductName;
TOP N, TOP N PERCENTRetrieves the top n or n percent of values, based on the order in which the records are sorted.
SELECT TOP 10 PERCENT UnitsInStock, ProductID, ProductName FROM Products ORDER BY UnitsInStock;
PARAMETERPrompts user to enter parameters for query that are used to restrict the result set.
SELECT ProductName, UnitPrice FROM Products WHERE ProductName Like [Enter first part of product name:] & "*" ORDER BY ProductName;

For more information about designing SQL statements, search the Microsoft Access Help index for "SQL statements."

© 2016 Microsoft