Chapter 4: Selecting Data with Queries
133
FIGURE 4.4The resulting datasheet of the query shown in Figure 4.3
In Figure 4.4, notice that you can’t tell which table provided the data in each column. In most
cases your users won’t know, nor will they care, where the data they see in an application comes
from. In this case, you know that the data is taken from four different tables, but the complexity of
the query is hidden from users. Access does an excellent job of connecting users to data, as this
query example shows.Types of queries
Access supports many different types of queries, grouped into six basic categories:l (^) Select: The most common type of query is the select query. As its name implies, a select
query selects information from one or more tables, creating a recordset. Generally speak-
ing, the data returned by a select query is updatable and is often used to populate forms
and reports.
l (^) Total: A total query is a special type of select query. Total queries provide sums or other
calculations (such as count) from the records returned by a select query. Selecting this
type of query adds a Total row in the Query by Example (QBE) grid.
l Action: An action query (Make-Table, Delete, Update, or Append) enables you to create
new tables or change data in existing tables. Action queries affect many records as a single
operation.
l (^) Crosstab: A crosstab query can display summary data in cross-tabular form like a spread-
sheet, with row and column headings based on fields in the table. The individual cells of
the recordset are computed or calculated from data in the underlying tables.
l Specialized queries: There are three specialized query types — union, pass-through, and
data definition. These queries are used for advanced database manipulation, such as work-
ing with client/server SQL databases like SQL Server or Oracle. You create these queries
by writing SQL statements that are specific to the server database.
l Top(n): Top(n) queries enable you to specify a number or percentage of records you want
returned from any type of query (select, total, and so on).