Create an SQL-specific query

Caution   If you convert a union query, pass-through query, or data-definition query to another type of query, such as a select query, you'll lose the SQL statement that you enter.

Create a union query that combines corresponding fields from two or more tables or queries into one field

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. Without adding tables or queries, click Close in the Show Table dialog box.
4. On the Query menu, point to SQL Specific, and then click Union.
5. Enter SQL SELECT statements combined with either the UNION operation if you don't want to return duplicate records or the UNION ALL operation if you do want to return duplicate records.

   Note   Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.

   

     This union query combines the values in the CompanyName and City fields from the Suppliers and Customers tables if the Country field is "Brazil".

6. If you want to specify sorting in a union query, add a single ORDER BY clause to the end of the last SELECT statement. In the ORDER BY clause, specify the field name to sort, which must come from the first SELECT statement.
7. To see the query's results, click View on the toolbar.

Note   A union query takes its column names from the column names in the first table or SELECT statement. If you want to rename a field in the results, use the AS clause to create an alias for the fields.

áCreate a pass-through query that sends commands to an SQL database

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. Without adding tables or queries, click Close in the Show Table dialog box.
4. On the Query menu, point to SQL Specific, and then click Pass-Through.
5. On the toolbar, click Properties to display the query property sheet.
6. In the query property sheet, set the ODBCConnectStr property to specify information on the database to which you want to connect. You can type the connection information, or click Build , and then enter information about the server you're connecting to.

   When you are prompted to save the password in the connection string, select Yes if you want the password and logon stored in the connection string information.

7. If the query isn't the type that returns records, set the ReturnsRecords property to No.
8. In the SQL Pass-Through Query window, type your pass-through query.

   For details on the syntax for your query, see the documentation for the SQL database server to which you're sending the query.

9. To run the query, click Run on the toolbar. (For a pass-through query that returns records, you can click View on the toolbar, instead.)

   If necessary, Microsoft Access prompts you for information about your SQL server database.

Notes

• If you don't specify a connection string in the ODBCConnectStr property, or if you delete an existing string, Access uses the default string "ODBC;". With this setting, Access prompts you for connection information each time you run the query.
• Some pass-through queries can return messages in addition to data. If you set the query's LogMessages property to Yes, Access creates a table that contains any returned messages. The table name is the user name concatenated with a hyphen (-) and a sequential number starting at 00. For example, the default user name is ADMIN so the tables returned would be named "ADMIN - 00," "ADMIN - 01," and so on.

Create a data-definition query to create, delete, or alter tables, or to create indexes in a database

1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
2. In the New Query dialog box, click Design View, and then click OK.
3. Without adding tables or queries, click Close in the Show Table dialog box.
4. On the Query menu, point to SQL Specific, and then click Data Definition.
5. Enter the SQL statement for your data-definition query. Each data-definition query consists of just one data-definition statement. 

   Supported data-definition statements

   SQL statement	Purpose
   CREATE TABLE	Creates a table
   ALTER TABLE	Adds a new field or constraint to an existing table
   DROP	Deletes a table from a database or removes an index from a field or group of fields
   CREATE INDEX	Creates an index for a field or group of fields

   To run the query, click Run on the toolbar.

Create a subquery to define a field or define criteria for a field

1. Create a new query.

   How?

   1. In the Database window, click Queries under Objects, and then click New on the Database window toolbar.
   2. In the New Query dialog box, click Design View, and then click OK.
   3. In the Show Table dialog box, click the tab that lists the tables or queries whose data you want to work with.
   4. Double-click the name of each object you want to add to the query, and then click Close.
   5. Add fields to the Field row in the design grid, and if you want, specify criteria and a sort order.
   6. To view the query's results, click View on the toolbar.
2. In query Design view, add the fields you want to the design grid, including the fields you want to use the subquery for.
3. If you're using a subquery to define criteria for a field, type a SELECT statement enclosed in parentheses in the Criteria cell of the field for which you want to set criteria.

   

   If you're using a subquery to define a Field cell, type a SELECT statement enclosed in parentheses in a Field cell. After you leave the cell, Microsoft Access automatically inserts "Expr1:", "Expr2:", and so on, before the SELECT statement.

   

   To rename the field, replace "Expr1:" with a field name ù for example, "Cat:".

   Note   For more space in which to enter the SELECT statement while in a Field or Criteria cell, press SHIFT+F2 and enter the statement in the Zoom box.

4. To see the results, click View on the toolbar.

Notes

• You can't use a field that you define with a subquery to group records.
• A subquery's SELECT statement can't define a union or crosstab query.