Writing SQL Statements

  • Article

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 type Description Example
Simple SELECT Selects specified fields (columns) from one or more tables. 
SELECT ProductID, ProductName FROM Products;
Totals Groups 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 JOIN Joins 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 JOIN Joins 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;
UNION Returns 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;
CROSSTAB Summarizes 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 PERCENT Retrieves 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;
PARAMETER Prompts 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."