Selecting Fields from a Single Table

As with other SQL dialects, Microsoft Jet SQL uses the SELECT statement to retrieve one or more fields from a given table. You may be familiar with the SQL statements generated by the Microsoft Access query design grid. Following is a simple example that selects all fields from the Customers table:

SELECT Customers.*
FROM Customers;

Here is another example generated by Microsoft Access, which selects particular fields rather than all fields:

SELECT 
	Products.ProductID, 
	Products.ProductName, 
	Products.UnitsInStock, 
	Products.UnitPrice*Products.UnitsInStock AS InventoryValue
FROM Products;

Note that when Microsoft Access displays this query it automatically qualifies each field reference with its base-table name. Microsoft Jet accepts a wide variety of table and field identifiers, including names with spaces and other punctuation. If an identifier contains spaces or any nonstandard characters, it must be enclosed in square brackets.

Microsoft Jet is flexible in its interpretation of SQL text strings. The previous query would work just as well if it were rewritten in a style slightly more familiar to experienced SQL users:

SELECT 
	P.ProductID, 
	P.ProductName, 
	P.UnitsInStock, 
	P.UnitPrice*P.UnitsInStock AS InventoryValue
FROM Products P;

This example uses the alias P to refer to the Products table and qualifies references to fields with that alias name. Although Microsoft Jet allows the syntax used above, queries built with the Microsoft Access query design grid add the AS reserved word in front of the alias name:

SELECT 
	P.ProductID, 
	P.UnitsInStock
FROM Products AS P;