How to Create a SQL Query in Microsoft Access

by Contributor

SQL queries use Structured Query Language (SQL), a standard scripting language, to make requests from databases. You can use four types of SQL queries in Access 97: union, pass-through, data-definition and subquery.

Creating a Subquery

Create a new select or action query. (See Related eHows for instructions.)

In Query Design view, add the desired fields to the design grid.

To use a subquery to define criteria for a field, type a SQL select statement in the Criteria cell. Set the select statement in parentheses. If you're using a subquery to define a Field cell, type a select statement in the Field cell instead.

Rename the fields if desired.

Click the View button to see the results.

Creating a Union Query

Open the Database window.

Click the Queries tab. Click New. The New Query dialog box opens.

Click Design View. Click OK. The Show Tables dialog box appears.

Don't add any tables. Click Close.

Click on the Query menu, then click on SQL Specific. Select Union.

Enter SQL select statements. Add the Union operation if you don't want duplicate records or the Union All operation if you do want duplicate records.

Click View to see your results.

Creating a Pass-Through Query and Creating a Data-Definition Query

Follow steps 1 through 4 above to create a pass-through query.

From the Query menu, click on SQL Specific and then on Pass-through.

From the View menu, select Properties. The query property sheet appears.

Set the ODBCConnectSTR property to define information about the database you intend to connect to. You will need information about the server.

Type your pass-through query in the SQL Pass-through Query window.

Click Run on the toolbar. If your query returns records, click View instead.

Follow steps 1 through 4 above to create a data-definition query.

Open the Query menu and select SQL Specific, then click on Data Definition.

Enter the SQL statement for your query. You can enter Create Table, Alter Table, Drop Table and Create Index.

Click the Run icon on the toolbar.

Tips

  • check Subqueries are made up of a SQL select statement located inside another select query or action query. Subqueries allow you to test to find out whether something exists or does not exist, to find values in the main query that are equal to, greater than or less than values in the subquery, or to create nested subqueries (that is, subqueries within subqueries).
  • check Union queries allow you to combine records from two or more tables.
  • check Select statements that you can use are Select, From, Where, Group By, Having, Order By, and With Owner Access Option.
  • check Pass-through queries retrieve records or change data on networked (ODBC) databases.
  • check Data-definition queries create or modify database objects like those found in Microsoft Access or in Microsoft SQL Server tables.

Items you will need