About SQL queries (MDB)

 Note   The information in this topic applies only to a Microsoft Access database (.mdb).

An SQL query is a query you create by using an SQL statement (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.). 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 (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.), 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 (SQL view: An object tab that displays the SQL statement for the current query or that is used to create an SQL-specific query (union, pass-through, or data definition). When you create a query in Design view, the SQL equivalent is constructed 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 (SQL-specific query: A query that consists of an SQL statement. Subqueries and pass-through, union, and data-definition queries are SQL-specific queries.), can't be created in the design grid (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE grid.). For pass-through (pass-through query: An SQL-specific query you use to send commands directly to an ODBC database server. By using pass-through queries, you work directly with the tables on the server instead of the data being processed by the Access database engine.), data-definition (data-definition query: An SQL-specific query that contains data definition language (DDL) statements. These statements allow you to create or alter objects in the database.), and union queries (union query: A query that uses the UNION operator to combine the results of two or more select queries.), you must create the SQL statements directly in SQL view. For subqueries (subquery: An SQL SELECT statement that is inside another select or action query.), you enter the SQL in the Field row or the Criteria row of the query design grid.

ShowWhere 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 Button image next to either of these properties, and then creating a query in query Design view.

You can also use SQL statements programmatically in:

ShowUsing 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 (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.) and property (property: A named attribute of a control, a field, or an object that you set to define one of the object's characteristics (such as size, color, or screen location) or an aspect of its behavior (such as whether the object is hidden).) settings. For example, you can use an SQL expression as a:

ShowSQL-specific queries

ShowUnion 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.

Combine data from two or more tables with a union query

Callout 1 The first SELECT statement returns two fields ...

Callout 2... the second SELECT statement returns two corresponding fields, and then ...

Callout 3... combines the values in the corresponding fields from both tables into one field.

ShowPass-through query

ShowData-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 (data type: The characteristic of a field that determines what type of data it can hold. Data types include Boolean, Integer, Long, Currency, Single, Double, Date, String, and Variant (default).) for each field in the table and assigns the FriendID field an index (index: A feature that speeds up searching and sorting in a table based on key values and can enforce uniqueness on the rows in a table. The primary key of a table is automatically indexed. Some fields can't be indexed because of their data type.) that marks it as the primary key (primary key: One or more fields (columns) whose values uniquely identify each record in a table. A primary key cannot allow Null values and must always have a unique index. A primary key is used to relate a table to foreign keys in other tables.).

CREATE TABLE Friends
([FriendID] integer,
[LastName] text,
[FirstName] text,
[Birthdate] date,
[Phone] text,
[Notes] memo,
CONSTRAINT [Index1] PRIMARY KEY ([FriendID]));

ShowSubquery

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 (design grid: The grid that you use to design a query or filter in query Design view or in the Advanced Filter/Sort window. For queries, this grid was formerly known as the QBE 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).
 
 
Applies to:
Access 2003