Using Advanced Queries

Microsoft Office Access provides a range of advanced queries. In this section, we describe
some of the most useful examples of those queries including:
n parameter queries;
n crosstab queries;
n Find Duplicates queries;
n Find Unmatched queries.

• Parameter Query

A parameter query displays one or more predefined dialog boxes that prompt the user for
the parameter value(s) (criteria). Parameter queries are created by entering a prompt
enclosed in square brackets in the Criteria cell for each field we want to use as a parameter.
For example, suppose that we want to amend the select query shown in Figure 7.6(a) to first
prompt for the owner’s first and last name before retrieving the property number and city

Figure 7.8 (a) QBE grid of select query to calculate the yearly rent for each property; (b) resulting datasheet; (c) equivalent SQL statement.

of his or her properties. The QBE grid for this parameter query is shown in Figure 7.9(a).

To retrieve the property details for an owner called ‘Carol Farrel’, we enter the appropriate

values into the first and second dialog boxes as shown in Figure 7.9(b), which results in

the display of the resulting datasheet shown in Figure 7.9(c). The equivalent SQL statement

is given in Figure 7.9(d).

• Crosstab Query

A crosstab query can be used to summarize data in a compact spreadsheet format. This

format enables users of large amounts of summary data to more easily identify trends and

Figure 7.9 (a) QBE grid of example parameter query; (b) dialog nboxes for first andlast name of owner (c)resulting datasheet; (d) equivalent SQL statement.

to make comparisons. When a crosstab query is run, it returns a snapshot. We can create

a crosstab query using the CrossTab Query Wizard or build the query from scratch using

the QBE grid. Creating a crosstab query is similar to creating a query with totals, but we

must specify the fields to be used as row headings, column headings, and the fields that are

to supply the values.

For example, suppose that we want to know for each member of staff the total number

of properties that he or she manages for each type of property. For the purposes of this

Figure 7.10 (a) QBE grid of example totals query; (b) resulting datasheet; (c) equivalent SQL statement.

example, we have appended additional property records into the PropertyForRent table to

more clearly demonstrate the value of crosstab queries. To answer this question, we first

design a totals query, as shown in Figure 7.10(a), which creates the datasheet shown in

Figure 7.10(b). The equivalent SQL statement for the totals query is given in Figure 7.10(c).

Note that the layout of the resulting datasheet makes it difficult to make comparisons

between staff.

Figure 7.11 (a) QBE grid of example crosstab query; (b) resulting datasheet; (c) equivalent SQL statement.

To convert the select query into a crosstab query, we change the type of query to

Crosstab, which results in the addition of the Crosstab row in the QBE grid. We then

identify the fields to be used for row headings, column headings, and to supply the values,

as shown in Figure 7.11(a). When we run this query, the datasheet is displayed in a more

compact layout, as illustrated in Figure 7.11(b). In this format, we can easily compare

figures between staff. The equivalent SQL statement for the crosstab query is given in

Figure 7.11(c). The TRANSFORM statement is not supported by standard SQL but is an

extension of Microsoft Office Access SQL.

• Find Duplicates Query

The Find Duplicates Query Wizard shown in Figure 7.2 can be used to determine if there

are duplicate records in a table or determine which records in a table share the same value.

For example, it is possible to search for duplicate values in the fName and lName fields to

Figure 7.12 (a) QBE for example Find Duplicates query; (b) resulting datasheet; (c) equivalent SQL statement.

determine if we have duplicate records for the same property owners, or to search for

duplicate values in a city field to see which owners are in the same city.

Suppose that we have inadvertently created a duplicate record for the property owner

called ‘Carol Farrel’ and given this record a unique owner number. The database therefore

contains two records with different owner numbers, representing the same owner. We

can use the Find Duplicates Query Wizard to identify the duplicated property owner

records using (for simplicity) only the values in the fName and lName fields. As discussed

earlier, the Wizard simply constructs the query based on our answers. Before viewing the

results of the query we can view the QBE grid for the Find Duplicates query shown in

Figure 7.12(a). The resulting datasheet for the Find Duplicates query is shown in 7.12(b)

displaying the two records representing the same property owner called ‘Carol Farrel’. The

equivalent SQL statement is given in Figure 7.12(c). Note that this SQL statement displays

in full the inner SELECT SQL statement that is partially visible in the Criteria row of the

fName field shown in Figure 7.12(a).

• Find Unmatched Query

The Find Unmatched Query Wizard shown in Figure 7.2 can be used to find records

in one table that do not have related records in another table. For example, we can find

clients who have not viewed properties for rent by comparing the records in the Client and

Viewing tables. The Wizard constructs the query based on our answers. Before viewing the

results of the query, we can view the QBE grid for the Find Unmatched query, as shown

in Figure 7.13(a). The resulting datasheet for the Find Unmatched query is shown in

7.13(b) indicating that there are no records in the Viewing table that relate to ‘Mike Ritchie’

in the Client table. Note that the Show box of the clientNo field in the QBE grid is not ticked

Figure 7.13 (a) QBE grid of example Find Unmatched query; (b) resulting datasheet; (c) equivalent SQL statement.

as this field is not required in the datasheet. The equivalent SQL statement for the QBE

grid is given in Figure 7.13(c). The Find Unmatched query is an example of a Left Outer

join, which we discussed in detail in Sections 4.1.3 and 5.3.7.

• Autolookup Query

An autolookup query can be used to automatically fill in certain field values for a new

record. When we enter a value in the join field in the query or in a form based on the query,

Microsoft Office Access looks up and fills in existing data related to that value. For

example, if we know the value in the join field (staffNo) between the PropertyForRent table

and the Staff table, we can enter the staff number and have Microsoft Office Access enter

the rest of the data for that member of staff. If no matching data is found, Microsoft Office

Access displays an error message.

To create an autolookup query, we add two tables that have a one-to-many relationship

and select fields for the query into the QBE grid. The join field must be selected

from the ‘many’ side of the relationship. For example, in a query that includes fields

from the PropertyForRent and Staff tables, we drag the staffNo field (foreign key) from the

PropertyForRent table to the design grid. The QBE grid for this autolookup query is shown

in Figure 7.14(a). Figure 7.14(b) displays a datasheet based on this query that allows us to

enter the property number, street, and city for a new property record. When we enter the

staff number of the member of staff responsible for the management of the property, for

example ‘SA9’, Microsoft Office Access looks up the Staff table and automatically fills in

the first and last name of the member of staff, which in this case is ‘Mary Howe’. Figure

7.14(c) displays the equivalent SQL statement for the QBE grid of the autolookup query.