You can use SQL statements in many places in Access where you can enter the name of a table, query, or field.
In some cases, Access fills in the SQL statement for you. For example, when you use a wizard to create a form or report that gets data from more than one table, Access automatically creates an SQL statement that it uses as the setting for the RecordSource property of the form or report. When you create a list box or combo box with a wizard, Access creates an SQL statement and uses it as the setting for the RowSource property of the list box or combo box.
Without using a wizard, you can generate an SQL statement for the RecordSource or RowSource properties by clicking the Build button 
next to either of these properties, and then creating a query in query Design view.
You can also use SQL statements programmatically in:
-
The SQL Statement argument of the RunSQL macro action.
-
Code as a literal string, or as an SQL statement that includes variables and controls.
-
The SQL property of a QueryDef object to change the underlying SQL statement of a query.
Using expressions in SQLYou can type an expression in an SQL SELECT statement, or in WHERE, ORDER BY, GROUP BY, or HAVING clauses. You can also type an SQL expression in several arguments and property settings. For example, you can use an SQL expression as a:
-
Where Condition argument of the OpenForm or ApplyFilter action.
-
Domain or criteria argument in a domain aggregate function.
-
Setting for the RecordSource or RowSource property in forms and reports.
A union query combines fields from two or more tables or queries into one field in the query's results. You might use a union query to combine data from two tables. For example, you might combine company name and city data for all companies in Brazil that are listed in the Suppliers and Customers tables. You could then create a make-table query based on the union query to make a new table.
The first SELECT statement returns two fields ...
... the second SELECT statement returns two corresponding fields, and then ...
... combines the values in the corresponding fields from both tables into one field.
A pass-through query sends commands directly to ODBC databases, such as Microsoft FoxPro, using commands that are accepted by the server. For example, you can use a pass-through query to retrieve records or change data. With pass-through queries, you work directly with the tables on the server instead of linking to them. Pass-through queries are also useful for running stored procedures on an ODBC server.
A data-definition query creates, deletes, or alters tables, or creates indexes in a database table. For example, the following data-definition query uses the CREATE TABLE statement to create a table named Friends. The statement includes the name and data type for each field in the table and assigns the FriendID field an index that marks it as the primary key.
CREATE TABLE Friends
([FriendID] integer,
[LastName] text,
[FirstName] text,
[Birthdate] date,
[Phone] text,
[Notes] memo,
CONSTRAINT [Index1] PRIMARY KEY ([FriendID]));
A subquery consists of an SQL SELECT statement inside another select query or action query. You can enter these statements in the Field row of the query design grid to define a new field, or in the Criteria row to define criteria for a field. You can use subqueries to:
- Test for a result from the subquery (by using the EXISTS or NOT EXISTS reserved words).
- Find any values in the main query that are equal to, greater than, or less than values returned by the subquery (by using the ANY, IN, or ALL reserved words).
- Create subqueries within subqueries (nested subqueries).