Where SQL statements are usedAn SQL query is a query you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Microsoft Access.
When you create a query in query Design view, Access constructs the equivalent SQL statements behind the scenes for you. In fact, most query properties in the property sheet in query Design view have equivalent clauses and options available in SQL view. If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it was previously in Design view.
Some SQL queries, called SQL-specific queries, can't be created in the design grid. For pass-through, data-definition, and union queries, you must create the SQL statements directly in SQL view. For subqueries, you enter the SQL in the Field row or the Criteria row of the query design grid.
Where SQL statements are used
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 SQL
You 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.
SQL-specific queries
Union query
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.
Pass-through query
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.
Data-definition query
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]));
Subquery
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: