Pass-Through
QueriesNulls
and Query ResultsParameter
Query Values from a FormYou learned the basics of query design in Chapter 4, “What Every Developer Needs to Know About Query Basics,” but Access has a wealth of query capabilities. In addition to the relatively simple Select queries covered in Chapter 4, you can create crosstab queries, union queries, self-join queries, and many other complex selection queries. You can also easily build Access queries that modify information, rather than retrieve it. This chapter covers these topics and the more advanced aspects of query design.
With action queries, you can easily modify data without writing code. In fact, using action queries is often a more efficient method than using code. Four types of action queries are available: update, delete, append, and make table. You use update queries to modify data in a table, delete queries to remove records from a table, append queries to add records to an existing table, and make table queries to create an entirely new table. Each type of query and its appropriate uses are explained in the following sections.
You use update queries to modify all records or any records meeting specific criteria. You can use an update query to modify the data in one field or several fields (or even tables) at one time (for example, a query that increases the salary of everyone in California by 10%). As mentioned, using update queries is usually more efficient than performing the same task with Visual Basic for Applications (VBA) code, so update queries are considered a respectable way to modify table data.
To build an update query, follow these steps:
StateProvince
has been added to the query grid because it will be used as a criterion for the update. DefaultRate
has been included because it’s the field that’s being updated.
Figure 12.1. An update query that increases the DefaultRate
for all clients in California.
StateProvince
has been set to CA
.Update
expression. In Figure 12.1, the DefaultRate
is being increased by 10%.Figure 12.2. The confirmation message you see when running an update query.
You should name Access update queries with the prefix qupd
. To adhere to standard naming conventions, you should give each type of action query a prefix indicating what type of query it is. Table 12.1 lists all the proper prefixes for action queries.
Table 12.1. Naming Prefixes for Action Queries
All Access queries are stored as Structured Query Language (SQL) statements. (Access SQL is discussed later in this chapter in the “Understanding SQL” section.) You can display the SQL for a query by selecting SQL view from the View drop-down on the Design tab. The SQL behind an Access update query looks like this:
The actions taken by an update query, as well as by all action queries, can’t be reversed. You must exercise extreme caution when running any action query.
Remember that if you have turned on the Cascade Update Related Fields Referential Integrity setting and the update query modifies a primary key field, the Access Database Engine updates the foreign key of all corresponding records in related tables. If you have not turned on the Cascade Update Related Fields option and referential integrity is being enforced, the update query doesn’t allow the offending records to be modified.
Rather than simply modify table data, delete queries permanently remove from a table any records meeting specific criteria; they’re often used to remove old records. You might want to delete all orders from the previous year, for example.
To build a delete query, follow these steps:
Figure 12.3. A delete query used to delete all time cards entered more than a year ago.
The SQL behind a delete query looks like this:
Viewing the results of an action query is often useful before you actually change the records included in the criteria. To view the records affected by the action query, click the Datasheet View button on the ribbon before you select Run. All records that will be affected by the action query appear in Datasheet view. If necessary, you can temporarily add key fields to the query to get more information about these records.
Remember that if you have turned on the Cascade Delete Related Records Referential Integrity setting, the Access Database Engine deletes all corresponding records in related tables. If you have not turned on the Cascade Delete Related Records option and you are enforcing referential integrity, the delete query doesn’t allow the offending records to be deleted. If you want to delete the records on the one side of the relationship, you must first delete all the related records on the many side.
With append queries, you can add records to an existing table. This is often done during an archive process. First, you append the records to be archived to the history table by using an append query. Next, you remove them from the master table by using a delete query.
To build an append query, follow these steps:
Figure 12.5. Identifying the table to which data will be appended and the database containing that table.
Figure 12.6. An append query that appends the EmployeeID
and DateEntered
of all employees entered in the year 2007 to another table.
DateEntered
in 2007 are appended to the destination table.The SQL behind an append query looks like this:
Append queries don’t allow you to introduce any primary key violations. If you’re appending any records that duplicate a primary key value, the message box shown in Figure 12.8 appears. If you go ahead with the append process, only those records without primary key violations are appended to the destination table.
Figure 12.8. The warning message you see when an append query and conversion, primary key, lock, or validation rule violation occurs.
An append query adds records to an existing table, but a make table query creates a new table, which is often a temporary table used for intermediary processing. You will often create a temporary table to freeze data while a user runs a report. By building temporary tables and running the report from those tables, you make sure users can’t modify the data underlying the report during the reporting process. Another common use of a make table query is to supply a subset of fields or records to a user.
To build a make table query, follow these steps:
Figure 12.9. Enter a name for the new table and select which database to place it in.
Figure 12.11. The make table query confirmation message box.
If you try to run the same make table query more than one time, the table with the same name as the table you’re creating is permanently deleted (see the warning message in Figure 12.12).
Figure 12.12. The make table query warning message displayed when a table already exists with the same name as the table to be created.
The SQL for a make table query looks like this:
As mentioned previously, action queries can be far more efficient than VBA code. Look at this example:
This subroutine uses ActiveX Data Objects (ADO) code to loop through tblEmployees
. It increases the billing rate by 1. Compare the ModifyRate
subroutine to the following code:
As you can see, the RunActionQuery
subroutine is much easier to code. The qupdBillingRate
query, shown in Figure 12.13, performs the same tasks as the ModifyRate
subroutine. In most cases, the action query runs more efficiently.
Figure 12.13. The qupdBillingRate
query increments the BillingRate
by 1.
An alternative to the two techniques shown previously is to use ADO code (rather than the DoCmd
object) to execute an action query. This technique is covered in detail in Chapter 15, “What Are ActiveX Data Objects, and Why Are They Important?”
Access 2007 queries have several properties that can dramatically change their behavior. To look up a query’s properties, right-click on a blank area in the top half of the Query window and select Properties to open the Property Sheet (see Figure 12.14). Chapter 4 discusses many of these properties. The following sections cover the Unique Values
, Unique Records
, and Top Values
properties.
Figure 12.14. Viewing the general properties for a query.
Unique Values
PropertyWhen set to Yes
, the Unique Values
property causes the query output to contain no duplicates for the combination of fields you include in it. Figure 12.15, for example, shows a query that includes the Country
and City
fields from tblClients
. The Unique Values
property in this example is set to No
, its default value. Notice that many combinations of countries and cities appear more than once. This happens whenever more than one client is found in a particular country and city. Compare this with Figure 12.16, in which the Unique Values
property is set to Yes
. Each combination of country and city appears only once.
Figure 12.15. A query with the Unique Values
property set to No
.
Figure 12.16. A query with the Unique Values
property set to Yes
.
Unique Records
PropertyIn Access 2000 and later, the default value for the Unique Records
property is No
. Setting it to Yes
causes the DISTINCTROW
statement to be included in the SQL statement underlying the query. When set to Yes
, the Unique Records
property denotes that the Access Database Engine includes only unique rows in the recordset underlying the query in the query result—and not just unique rows based on the fields in the query result. The Unique Records
property applies only to multitable queries; the Access Database Engine ignores it for queries that include only one table.
Top Values
PropertyThe Top Values
property enables you to specify a certain percentage or a specific number of records that the user wants to view in the query result. For example, you can build a query that outputs the country/city combinations with the top 10 sales amounts. You can also build a query that shows the country/city combinations whose sales rank in the top 50%. You can specify the Top Values
property in a few different ways. Here are two examples:
Top Values
combo box on the ribbon and choose from the predefined list of choices (this combo box is not available for certain field types).Top Values
property in the Query Properties window, or select one of the predefined entries from the drop-down list for the property.Figure 12.17 illustrates the design of a query showing the companies with the top 25% of sales. This Total
query summarizes the result of the BillableHours
multiplied by the BillingRate
for each company. Notice that the Top Values
property is set to 25%
. The output of the query is sorted in descending order by the result of the BillableAmount
calculation (see Figure 12.18). If the SaleAmount
field were sorted in ascending order, the bottom 10% of the sales amount would be displayed in the query result. Remember that the field(s) you want to use to determine the top values must appear as the left-most field(s) in the query’s sort order.
Figure 12.17. A Total
query that retrieves the top 25% of the billable amounts.
Figure 12.18. The result of a Total
query showing the top 25% of the billable amounts.
You might be surprised to discover that the Top Values
property doesn’t always seem to accurately display the correct number of records in the query result. The Access Database Engine returns all records with values that match the value in the last record as part of the query result. In a table with 100 records, for example, the query asks for the top 10 values. Twelve records will appear in the query result if the 10th, 11th, and 12th records all have the same value in the field being used to determine the top value.
The Access Database Engine includes an Optimizer that looks at how long it takes to perform each task needed to produce the required query results. It then produces a plan for the shortest path to get the results that you want. This plan is based on several statistics:
The statistics just listed are updated whenever the query is compiled. For a query to be compiled, it must be flagged as needing to be compiled. The flag can be any of the following occurrences:
After the Access Database Engine flags a query as needing to be compiled, it isn’t compiled until the next time the query is run. During compiling, which takes 1–4 seconds, all statistics are updated, and a new optimization or Query Plan is produced.
Because a Query Plan is based on the number of records in each table included in the query, you should open and save your queries each time the volume of data in a table changes significantly. This is especially true when you’re moving your query from a test environment to a production environment. If you test your application with a few records in each table and the table’s production data soon grows to thousands of records, your query will be optimized for only a few records and won’t run efficiently. I handle this problem by compacting the production database on a regular basis.
When you’re analyzing the time it takes for a particular query to run, it’s important to time two tasks:
The first measurement is fairly obvious; it measures the amount of time it takes from the moment the Run button is clicked on the ribbon until the first screen of data is displayed. The second measurement is a little less obvious; it involves waiting until the N
value in Record 1 of N
displays at the bottom of the query result. The two measurements might be the same, if the query returns only a small number of records. The Access Database Engine decides whether it’s more efficient to run the query and then display the query results, or to display partial query results while the query continues to run in the background.
The Performance Analyzer can analyze your queries to determine whether additional indexes will improve query performance. It’s important to run the Performance Analyzer with the same volume of data that will be present in the production version of your tables. The Performance Analyzer is covered in Chapter 18, “Optimizing Your Application.”
You can take many steps to improve a query’s performance. They include, but aren’t limited to, the following techniques:
CustID
field instead of specifying the CompanyName
field as the primary key for the table.State
table, for example, instead of displaying a unique list of states based on all the states currently included in the Customer
table, build a separate State
table and use that in your queries.Like
in the query criteria, try to place the asterisk at the end of the character string rather than at the beginning. When you place the asterisk at the end of a string, as in Like Th*
, an index can be used to improve query performance. If you place the asterisk at the beginning of a string, as in Like *Sr
, the Access Database Engine cannot use any indexes.Count(*)
rather than Count([
fieldname
])
when counting how many records meet a particular set of criteria. Count(*)
simply tallies up the total number of records, but Count([
fieldname
])
actually checks to see whether the value is Null
, which would exclude the record from the total computation. Furthermore, as mentioned in the next section on Rushmore technology, the Count(*)
function is highly optimized by Rushmore.Group By
as little as possible. When possible, use First
instead. For example, if you’re totaling sales information by order date and order number, you can use First
for the order date and group by order number. The reason is that all records for a given order number automatically occur on the same order date.One of the most important lessons to learn about the tips listed here is that you shouldn’t follow them blindly. Query optimization is an art, not a science. What helps in some situations might actually do harm in others, so it’s important to perform benchmarks with your actual system and data.
Rushmore is a data-access technology that can help improve processing queries. You can use Rushmore technology only when you include certain types of expressions in the query criteria. It won’t automatically speed up all your queries. You must construct a query in a certain way for the query to benefit from Rushmore.
Rushmore can optimize a query with an expression and a comparison operator as the criteria for an Indexed
field. The comparison operator must be <
, >
, =
, <=
, >=
, <>
, Between
, Like
, or In
.
The expression can be any valid expression, including constants, functions, and fields from other tables. Here are some examples of expressions that Rushmore can optimize:
[Age] > 50
[OrderDate] Between #1/1/2007# And #12/31/2007#
[State] = "CA"
Rushmore can also optimize queries that include complex expressions combining the And
and Or
operators. If Rushmore can optimize both expressions, the query will be fully optimized. However, if Rushmore can optimize only one expression and you combine the expressions with an And
, the query will be partially optimized. If Rushmore can fully optimize only one expression and you combine the expressions with an Or
, the query won’t be optimized.
You should remember a few important concepts about Rushmore:
Not
operator can’t be optimized.Count(*)
function is highly optimized by Rushmore.=
.LastName
field in combination with the FirstName
field, the index can be used to search on LastName
or on a combination of LastName
and FirstName
, but it can’t be used in an expression based on the FirstName
field.A crosstab
query summarizes query results by displaying one field in a table down the left side of the datasheet and additional facts across the top of the datasheet. A crosstab
query can, for example, summarize the dollars sold by a salesperson to each company. You can place the name of each company in the query output’s left-most column, and you can display each salesperson across the top. The dollars sold appear in the appropriate cell of the query output (see Figure 12.19).
Figure 12.19. An example of a crosstab
query that shows the dollars sold to each company by salesperson.
Crosstab
queries are probably one of the most complex and difficult queries to create. For this reason, Microsoft offers a Crosstab Query Wizard. The following sections explain the methods for creating a crosstab
query with and without the Crosstab Query Wizard.
Follow these steps to design a crosstab
query with the Crosstab Query Wizard:
crosstab
query on another query that has the tables and fields you want. Click Next.
Figure 12.20. The first step of the wizard asks you to select a table or query on which you want to base the crosstab query.
CompanyName
field is selected as the row heading. Click Next.
Employee
field is selected as the column heading. Click Next.
Total
field is totaled for each company and employee. Click Next.
Figure 12.23. Specifying the field you want the crosstab
query to use for calculating.
Figure 12.24 shows a completed crosstab query in Design view; take a look at several important attributes. Notice the Crosstab row of the query grid. The CompanyName
field is specified as the row heading and is used as Group By columns for the query. The Employee
field is included as a column heading; it is also used as a Group By for the query.
Figure 12.24. A completed crosstab query in Design view.
The Total
is specified as a value. The Total cell for the column indicates that this field will be summed (as opposed to being counted, averaged, and so on).
Notice the column labeled Total of Total
. This column displays the total of all the columns in the query. It’s identical to the column containing the value except for the alias in the field name and the fact that the Crosstab cell is set to Row Heading
rather than Value
.
Although you can create many of your crosstab queries by using the Crosstab Query Wizard, you should know how to build one without the wizard. This knowledge lets you modify existing crosstab queries and gain ultimate control over creating new queries.
To build a crosstab query without using the Crosstab Query Wizard, follow these steps:
Row Heading
from the drop-down list.Column Heading
from the drop-down list.Value
from the Crosstab drop-down list.Figure 12.25 shows a query in which the column heading is set to the month of the ProjectBeginDate
field; the row heading is set to the EmployeeName
field. The sum of the ProjectTotalEstimate
field is the value for the query. The ProjectBeginDate
is also included in the query grid as a WHERE
clause for the query. Figure 12.26 shows the results of running the query.
Figure 12.25. A crosstab query, designed without a wizard, showing the project total estimate by employee and month.
Figure 12.26. The result of running the crosstab query shown in Figure 12.25.
If you don’t use fixed column headings, all the columns are included in the query output in alphabetical order. For example, if you include month names in the query result, they appear as Apr, Aug, Dec, Feb, and so on. By using fixed column headings, you tell Access the order in which each column appears in the query result. You can specify column headings by setting the query’s Column Headings
property (see Figure 12.27).
Figure 12.27. A query’s Column Headings
property.
All fixed column headings must match the underlying data exactly; otherwise, information will be omitted inadvertently from the query result. For example, if the column heading for the month of June was accidentally entered as June
and the data output by the format statement included data for the month of Jun
, all June data would be omitted from the query output.
Regardless of how crosstab queries are created, you should be aware of some special caveats when working with them:
Value
field. If you do, you get the error message You can't specify criteria on the same field for which you enter a Value in the Crosstab row
. If you must specify criteria for the Value
field, you must first build another query that includes your selection criteria and base the crosstab query on the first query.crosstab
query must be explicitly declared in the Query Parameters dialog box.Pivot tables, introduced with Access 2002, have all the functionality of crosstab queries and then some! Consider replacing crosstab queries with Select
queries stored in PivotTable view.
You use outer joins when you want the records on the one side of a one-to-many relationship to be included in the query result, regardless of whether there are matching records in the table on the many side. With a Customers
table and an Orders
table, for example, users often want to include only customers with orders in the query output. An inner join (the default join type) does this. In other situations, users want all customers to be included in the query result, whether or not they have orders. This is when an outer join is necessary.
In Access, there are two types of outer joins: left outer joins and right outer joins. A left outer join occurs when all records on the “one” side of a one-to-many relationship are included in the query result, regardless of whether any records exist on the “many” side. A right outer join means all records on the “many” side of a one-to-many relationship are included in the query result, regardless of whether there are any records on the “one” side. A right outer join should never occur if you are enforcing referential integrity.
To establish an outer join, you must modify the join between the tables included in the query:
tblClients
and Only Those Records from tblProjects
Where the Joined Fields Are Equal.
The SQL statement produced when a left outer join is established looks like this:
SELECT DISTINCTROW tblClients.ClientID, tblClients.CompanyName
FROM tblClients
LEFT JOIN tblProjects ON tblClients.ClientID = tblProjects.ClientID;
A left outer join can also be used to identify all the records on the “one” side of a join that don’t have corresponding records on the “many” side. To do this, simply enter Is Null
as the criterion for any required field on the “many” side of the join. A common solution is to place the criterion on the foreign key field. In the query shown in Figure 12.29, only clients without projects are displayed in the query result.
Figure 12.29. A query showing clients without projects.
A self-join enables you to join a table to itself. This is often done so that information in a single table can appear to exist in two separate tables. A classic example is seen with employees and supervisors. Two fields are included in the Employees
table: One field includes the EmployeeID
of the employee being described in the record, and the other field specifies the EmployeeID
of the employee’s supervisor. If you want to see a list of employee names and the names of their supervisors, you’ll need to use a self-join.
To build a self-join query, follow these steps:
1
.Alias
property as desired. In Figure 12.30, the alias has been changed to Supervisors
.
SupervisorID
field of the tblEmployees
table has been joined with the EmployeeID
field from the aliased table.
Figure 12.31. Establishing a self-join between the table and its alias.
FirstName
and LastName
fields are included from the tblEmployees
table. The SupervisorName
expression (a concatenation of the supervisor’s first and last names) is supplied from the copy of the table with the Supervisors
alias.You can permanently define self-relationships in the Relationships window. You will often do this so that you can establish referential integrity between two fields in the same table. In the example of employees and supervisors, you can establish a permanent relationship with referential integrity to make sure supervisor ID numbers aren’t entered with employee ID numbers that don’t exist.
To learn more about referential integrity, see Chapter 3, “Relationships: Your Key to Data Integrity.”
Access SQL is the language that underlies Access queries, so you need to understand a little bit about it, where it came from, and how it works. Access SQL enables you to construct queries without using the Access Query By Example (QBE) grid. This is necessary, for example, if you must build a SQL statement on the fly in response to user interaction with your application. Furthermore, certain operations supported by Access SQL aren’t supported by the graphical QBE grid. You must build these SQL statements in the Query Builder’s SQL view. In addition, many times you will want to build the record source for a form or report on the fly. In those situations, you must have command of the SQL language. Finally, you will want to use SQL statements in your ADO code. For all these reasons, learning SQL is a valuable skill.
SQL is a standard from which many different dialects have emerged. It was developed at an IBM research laboratory in the early 1970s and first formally described in a research paper released in 1974 at an Association for Computing Machinery meeting. Jet 4.0, the version of the Jet Engine provided with Access 2000 and above, has two modes: One supports Access SQL, and the other supports SQL-92. The Access Database Engine, included with Access 2007, also supports the SQL-92 extensions. The SQL-92 extensions are not available from the user interface. They can only be accessed using ADO. They are covered in a later section of this chapter, “Understanding Jet 4.0 ANSI-92 Extensions.”
At the very least, you need to understand SQL’s basic constructs, which enable you to select, update, delete, and append data by using SQL commands and syntax. Access SQL is made up of very few verbs. The sections that follow cover the most commonly used verbs.
SQL is easy to learn. When retrieving data, you simply build a SELECT
statement. SELECT
statements are composed of clauses that determine the specifics of how the data is selected. When they’re executed, SELECT
statements select rows of data and return them as a recordset.
In the examples that follow, keywords appear in uppercase. Values that you supply appear italicized. Optional parts of the statement appear in square brackets. Curly braces, combined with vertical bars, indicate a choice. Finally, ellipses are used to indicate a repeating sequence.
SELECT
StatementThe SELECT
statement is at the heart of the SQL language. It is used to retrieve data from one or more tables. Its basic syntax is
SELECT column-list FROM table-list WHERE where-clause ORDER BY order-by-clause
SELECT
ClauseThe SELECT
clause specifies what columns you want to retrieve from the table whose data is being returned to the recordset. The basic syntax for a SELECT
clause is
SELECT column-list
The simplest SELECT
clause looks like this:
SELECT *
This SELECT
clause retrieves all columns from a table. Here’s another example that retrieves only the ClientID
and CompanyName
columns from a table:
SELECT ClientID, CompanyName
You not only can include columns that exist in your table, but also can include expressions in a SELECT
clause. Here’s an example:
SELECT ClientID, City & ", " & State & " " & PostalCode AS Address
This SELECT
clause retrieves the ClientID
column as well as an alias called Address
, which includes an expression that concatenates the City
, State
, and PostalCode
columns.
FROM
ClauseThe FROM
clause specifies the tables or queries from which the records should be selected. It can include an alias you use to refer to the table. The FROM
clause looks like this:
FROM table-list [AS alias]
Here’s an example of a basic FROM
clause:
FROM tblClients AS Clients
In this case, the name of the table is tblClients
, and the alias is Clients
. If you combine the SELECT
clause with the FROM
clause, the SQL statement looks like this:
SELECT ClientID, CompanyName FROM tblClients
This SELECT
statement retrieves the ClientID
and CompanyName
columns from the tblClients
table.
Just as you can alias the fields included in a SELECT
clause, you can also alias the tables included in the FROM
clause. The alias is used to shorten the name, to simplify a cryptic name, and to perform a variety of other functions. Here’s an example:
SELECT ClientID, CompanyName FROM tblClients AS Customers
This SQL statement selects the ClientID
and CompanyName
fields from the tblClients
table, aliasing the tblClients
table as Customers
.
WHERE
ClauseThe WHERE
clause limits the records retrieved by the SELECT
statement. You must follow several rules when building a WHERE
clause. The text strings that you are searching for must be enclosed in quotation marks. Dates must be surrounded by pound (#) signs. Finally, you must include the keyword LIKE
when using wildcard characters. A WHERE
clause can include up to 40 columns combined by the keywords AND
and OR
. The syntax for a WHERE
clause looks like this:
WHERE expression1 [{AND|OR} expression2 [...]]
A simple WHERE
clause looks like this:
WHERE Country = "USA"
Using an AND
to further limit the criteria, the WHERE
clause looks like this:
WHERE Country = "USA" AND ContactTitle Like "Sales*"
This WHERE
clause limits the records returned to those in which the country is equal to USA
and the ContactTitle
begins with Sales
. Using an OR
, the SELECT
statement looks like this:
WHERE Country = "USA" OR Country = "Canada"
This WHERE
clause returns all records in which the country is equal to either USA
or Canada
. Compare that with the following example:
WHERE Country = "USA" OR ContactTitle Like "Sales*"
This WHERE
clause returns all records in which the Country
is equal to USA
or the ContactTitle
begins with Sales
. For example, if the ContactTitle
for the salespeople in China begins with Sales
, the names of those salespeople will be returned from this WHERE
clause. The WHERE
clause combined with the SELECT
and FROM
clauses looks like this:
Although Access SQL uses quotation marks to surround text values you’re searching for, the ANSI-92 standard dictates that apostrophes (single quotation marks) must be used to delimit text values.
ORDER BY
ClauseThe ORDER BY
clause determines the order in which the returned rows are sorted. It’s an optional clause, and it looks like this:
ORDER BY column1 [{ASC|DESC}], column2 [{ASC|DESC}] [,...]]
Here’s an example:
ORDER BY ClientID
The ORDER BY
clause can include more than one field:
ORDER BY Country, ClientID
When more than one field is specified, the leftmost field is used as the primary level of sort. Any additional fields are the lower sort levels. Combined with the rest of the SELECT
statement, the ORDER BY
clause looks like this:
The ORDER BY
clause allows you to determine whether the sorted output appears in ascending or descending order. By default, output appears in ascending order. To switch to descending order, use the optional keyword DESC
. Here’s an example:
SELECT ClientID, CompanyName FROM tblClients ORDER BY ClientID DESC
This example selects the ClientID
and CompanyName
fields from the tblClients
table, ordering the output in descending order by the ClientID
field.
JOIN
ClauseOften you’ll need to build SELECT
statements that retrieve data from more than one table. When building a SELECT
statement based on more than one table, you must join the tables with a JOIN
clause. The JOIN
clause differs depending on whether you join the tables with an INNER JOIN
, a LEFT OUTER JOIN
, or a RIGHT OUTER JOIN
.
The SQL-89 and SQL-92 syntax for joins differs. The basic SQL-89 syntax is
SELECT column-list FROM table1, table2 WHERE table1.column1 = table2.column2
The SQL-92 syntax is preferred because it separates the join from the WHERE
clause. It is
Note that the keyword OUTER
is optional.
Here’s an example of a simple INNER JOIN
:
Notice that four columns are returned in the query result. Two columns are from tblClients
and two are from tblProjects
. The SELECT
statement uses an INNER JOIN
from tblClients
to tblProjects
based on the ClientID
field. This means that only clients who have projects are displayed in the query result. Compare this with the following SELECT
statement:
This SELECT
statement joins the two tables using a LEFT JOIN
from tblClients
to tblProjects
based on the ClientID
field. All clients are included in the resulting records, whether or not they have projects.
Sometimes you will need to join more than two tables in a SQL statement. When you need to do this, the ANSI-92 syntax is
FROM table1 JOIN table2 ON condition1 JOIN table3 ON condition2
The following example joins the tblClients
, tblProjects
, and tblPayments
tables:
In the example, the order of the joins is unimportant. The exception to this is when inner and outer joins are combined. When combining inner and outer joins, the Access Database Engine applies two specific rules. First, the nonpreserved table in an outer join cannot participate in an inner join. The nonpreserved table is the one whose rows might not appear. In the case of a left outer join from tblClients
to tblProjects
, the tblProjects
table is considered the nonpreserved table. It therefore cannot participate in an inner join with tblPayments
. The second rule is that the nonpreserved table in an outer join cannot participate with another nonpreserved table in another outer join.
Self-joins were covered earlier in this chapter. The SQL syntax required to create them is similar to a standard join and is covered here:
Notice that the tblEmployees
table is joined to an alias of the tblEmployees
table that is referred to as tblSupervisors
. The SupervisorID
from the tblEmployees
table is joined with the EmployeeID
field from the tblSupervisors
alias. The fields included in the output are the FirstName
and LastName
from the tblEmployees
table and the FirstName
and LastName
from the alias of the tblEmployees
table.
So far, all the joins that we have covered involve situations in which the value of a field in one table is equal to the value of the field in the other table. You can create non-equi joins in which the >
, >=
, <
, <=
, <>
, or Between
operator is used to join two tables. Here’s an example:
This example returns only the rows from tblProjects
where the ProjectBeginDate
is on or after the IntroDate
stored in the tblClients
table.
ALL
, DISTINCTROW
, and DISTINCT
ClausesThe ALL
clause of a SELECT
statement means that all rows meeting the WHERE
clause are included in the query result. When the DISTINCT
keyword is used, Access eliminates duplicate rows, based on the fields included in the query result. This is the same as setting the Unique Values
property to Yes
in the graphical QBE grid. When the DISTINCTROW
keyword is used, Access eliminates any duplicate rows based on all columns of all tables included in the query (whether they appear in the query result or not). This is the same as setting the Unique Records
property to Yes
in the graphical QBE grid. These keywords in the SELECT
clause look like this:
SELECT [{ALL|DISTINCT|DISTINCT ROW}] column-list
TOP
PredicateThe Top Values
property, available via the user interface, is covered in the “Viewing Special Query Properties” section, earlier in this chapter. The keyword TOP
is used to implement this feature in SQL. The syntax looks like this:
SELECT [{ALL|DISTINCT|DISTINCTROW}] [TOP n [PERCENT]] column-list
The example that follows extracts the five clients whose IntroDate
field is most recent:
GROUP BY
ClauseThe GROUP BY
clause is used to calculate summary statistics; it’s created when you build a Totals
query by using the graphical QBE grid. The syntax of the GROUP BY
clause is
GROUP BY group-by-expression1 [,group-by-expression2 [,...]]
The GROUP BY
clause is used to dictate the fields on which the query result is grouped. When multiple fields are included in a GROUP BY
clause, they are grouped from left to right. The output is automatically ordered by the fields designated in the GROUP BY
clause. In the following example, the SELECT
statement returns the country, city, and total freight for each country/city combination. The results are displayed in order by country and city:
The GROUP BY
clause indicates that detail for the selected records isn’t displayed. Instead, the fields indicated in the GROUP BY
clause are displayed uniquely. One of the fields in the SELECT
statement must include an aggregate function. This result of the aggregate function is displayed along with the fields specified in the GROUP BY
clause.
HAVING
ClauseA HAVING
clause is similar to a WHERE
clause, but it differs in one major respect: It’s applied after the data is summarized rather than before. In other words, the WHERE
clause is used to determine which rows are grouped. The HAVING
clause determines which groups are included in the output. A HAVING
clause looks like this:
HAVING expression1 [{AND|OR} expression2[...]]
In the following example, the criterion > 1000
will be applied after the aggregate function SUM
is applied to the grouping:
You can practice entering and working with SQL statements in two places:
In the following sections, you look at both of these techniques.
A great place to practice writing SQL statements is in the SQL View window of a query. It works like this:
You can also execute SQL statements directly from VBA code. You can run a SQL statement from VBA code in two ways:
The VBA language enables you to build a query on the fly, execute it, and never store it. The code looks like this:
Working with recordsets is covered extensively in Chapter 15. For now, you need to understand that this code creates a temporary query definition using a SQL statement. In this example, the query definition is never added to the database. Instead, the SQL statement is executed but never stored.
A SQL statement can also be provided as part of the recordset’s Open
method. The code looks like this:
Again, this code is discussed more thoroughly in Chapter 15. Notice that the Open
method of the recordset object receives two parameters: The first is a SELECT
statement, and the second is the Connection
object.
A union query enables you to combine data from two tables with similar structures; data from each table is included in the output. For example, suppose you have a tblTimeCards
table containing active time cards and a tblTimeCardsArchive
table containing archived time cards. The problem occurs when you want to build a report that combines data from both tables. To do this, you must build a union query as the record source for the report. The syntax for a union query is
This example combines data from the tblEmployees
table with data from the tblSummerEmployees
table, preserving duplicate rows, if there are any.
ALL
KeywordNotice the keyword ALL
in the previous SQL statement. By default, Access eliminates all duplicate records from the query result. This means that if an employee is found in both the tblEmployees
and tblSummerEmployees
tables, he appears only once in the query result. Including the keyword ALL
causes any duplicate rows to display.
When sorting the results of a union query, you must include the ORDER BY
clause at the end of the SQL statement. Here’s an example:
This example combines data from the tblEmployees
table with data from the tblSummerEmployees
table, preserving duplicate rows, if there are any. It orders the results by the Salary
field (combining the data from both tables).
If the column names that you are sorting by differ in the tables included in the union query, you must use the column name from the first table.
You can use the graphical QBE to create a union query. The process is as follows:
UNION
clause. Notice that you can’t switch back to the query’s Design view (see Figure 12.32).
Figure 12.32. An example of a union query that combines tblTimeCards
with tblTimeCardsArchive
.
If you build a query and then designate the query as an SQL Specific
query, you lose everything that you did prior to the switch. There is no warning, and Undo is not available!
It is important to note that the result of a union query is not updateable. Furthermore, the fields in each SELECT
statement are matched only by position. This means that you can get strange results by accidentally listing the FirstName
field followed by the LastName
field in the first SELECT
statement, and the LastName
field followed by the FirstName
field in the second SELECT
statement. Each SELECT
statement included in a union query must contain the same number of columns. Finally, each column in the first SELECT statement much have the same data type as the corresponding column in the second SELECT statement.
Pass-Through
QueriesPass-Through
queries enable you to send uninterpreted SQL statements to your back-end database when you’re using something other than the Access Database Engine. These uninterpreted statements are in the SQL that’s specific to your particular back end. Although the Access Database Engine sees these SQL statements, it makes no attempt to parse or modify them. Pass-Through
queries are used in several situations:
Pass-Through
query, the join is done in the memory of the user’s PC after all the required data has been sent over the network.Although Pass-Through
queries offer many advantages, they aren’t a panacea. They do have a few disadvantages:
Pass-Through
query to access SQL Server data, you must write the SQL statement in T-SQL. When writing a Pass-Through
query to access Oracle data, you must write the SQL statement in PL-SQL. This means that you’ll need to rewrite all the SQL statements if you switch to another back end.Pass-Through
query can’t be updated.Now that you know all the advantages and disadvantages of Pass-Through
queries, you can learn how to build one:
Figure 12.33. A SQL Pass-Through
query that selects specific fields from the Sales
table, which resides in the PublisherInfo
data source.
Null
s and Query ResultsNull
values can wreak havoc with your query results because they propagate. Look at the query in Figure 12.34. Notice that when parts and labor are added, and either the Parts
field or the Labor
field contains a Null
, the result of adding the two fields is Null
. In Figure 12.35, the problem is rectified. Figure 12.36 shows the design of the query that eliminates the propagation of the Null
s. Notice the expression that adds the two values:
TotalPrice: Nz([Parts]) + Nz([Labor])
Figure 12.34. An example that shows the propagation of Null
s in a query result.
Figure 12.35. An example that shows Null
s eliminated from the query result.
Figure 12.36. A solution to eliminate propagation of Null
s.
This expression uses the Nz
function to convert the Null
values to 0 before the two field values are added together.
Subqueries allow you to embed one SELECT
statement within another. By placing a subquery in a query’s criteria, you can base one query on the result of another. Figure 12.37 shows an example. The query pictured finds all the clients without projects.
Figure 12.37. A query containing a subquery.
The SQL statement looks like this:
This query first runs the SELECT
statement SELECT ClientID from tblProjects
. It uses the result as criteria for the first query.
SQL can be used not only to retrieve data, but to update it as well. This concept was introduced in the section “Using Action Queries,” which focused on the SQL statements behind the action queries.
UPDATE
StatementThe UPDATE
statement is used to modify the data in one or more columns of a table. The syntax for the UPDATE
statement is
The WHERE
clause in the UPDATE
statement is used to limit the rows that are updated. The following is an example of an UPDATE
statement:
This statement updates the DefaultRate
column of the tblClients
table, increasing it by 10% for any clients that have a default rate less than or equal to 125.
DELETE
StatementWhereas the UPDATE
statement is used to update all rows that meet specific criteria, the DELETE
statement deletes all rows that meet the specified criteria. The syntax for the DELETE
statement is
DELETE FROM table [WHERE criteria]
As with the UPDATE
statement, the WHERE
clause is used to limit the rows that are deleted. The following is an example of the use of a DELETE
statement:
This statement deletes all clients from the tblClients
table whose DefaultRate
field is less than or equal to 125.
INSERT INTO
StatementThe INSERT INTO
statement is used to copy rows from one table to another. The syntax for the INSERT INTO
statement is
INSERT INTO target-table select-statement [WHERE criteria]
Once again, the optional WHERE
clause is used to limit the rows that are copied. Here’s an example:
This statement inserts the ClientID
, CompanyName
, ContactFirstName
, ContactLastName
, ContactTitle
, and DefaultRate
fields into the corresponding fields in the tblCheapClients
table for any clients whose DefaultRate
field is less than or equal to 125.
SELECT INTO
StatementWhereas the INSERT INTO
statement inserts data into an existing table, the SELECT INTO
statement inserts data into a new table. The syntax looks like this:
The WHERE
clause is used to determine which rows in the source table are inserted into the destination table. The ORDER BY
clause is used to designate the order of the rows in the destination table. Here’s an example:
This statement inserts data from the selected fields in the tblClients
table into a new table called tblCheapClients
. Only the clients whose DefaultRate
field is less than or equal to 125
are inserted.
Access 2007 offers two methods of programmatically defining and modifying objects. You can use either ActiveX Data Object Extensions for DDL and Security (ADOX) or Data Definition Language (DDL). DDL is covered in this chapter. ADOX is introduced in Chapter 15.
CREATE TABLE
StatementAs its name implies, the CREATE TABLE
statement is used to create a new table. The syntax is
You must designate the type of data for each column included in the table. When defining a text field, you can also specify the size parameter. Notice that constraints are available at the table level and at the field level. Here’s an example of a CREATE TABLE
statement:
This example creates a table named tblCustomers
. The table will contain three fields: CustomerID
(Long), CompanyName
(Text), and IntroDate
(DateTime).
The CONSTRAINT
clause allows you to create primary and foreign keys. It looks like this:
CONSTRAINT name {PRIMARY KEY|UNIQUE|REFERENCES foreign-table [foreign-column]}
Here’s an example:
The example creates a primary key index based on the CustomerID
field.
CREATE INDEX
StatementThe CREATE INDEX
statement is used to add an index to an existing table. It is supported in Access but is not part of the ANSI standard. It looks like this:
The example creates an index called CompanyName
, based on the CompanyName
field.
ALTER TABLE
StatementThe ALTER TABLE
statement is used to modify the structure of an existing table. The syntax has four forms. The first form looks like this:
ALTER TABLE table-name ADD [COLUMN] column-name datatype [(size)]
[CONSTRAINT column-constraint]
This form of the ALTER TABLE
statement adds a column to an existing table. Here’s an example:
ALTER TABLE tblCustomers ADD ContactName Text 50
The second form uses the following syntax to delete a column from an existing table:
ALTER TABLE table-name DROP [COLUMN] column-name
Here’s an example:
ALTER TABLE tblCustomers DROP COLUMN ContactName
The third form uses the ALTER TABLE
statement to add a constraint to an existing column. The syntax is
ALTER TABLE table-name ADD CONSTRAINT constraint
Here’s an example:
ALTER TABLE tblCustomers ADD CONSTRAINT CompanyName UNIQUE (CompanyName)
Finally, the fourth form drops a constraint from an existing column:
ALTER TABLE table-name DROP CONSTRAINT index
Here’s an example:
ALTER TABLE tblCustomers DROP CONSTRAINT CompanyName
DROP INDEX
StatementThe DROP INDEX
statement is used to remove an index from a table. The syntax is as follows:
DROP INDEX index ON table-name
DROP INDEX CompanyName ON tblCustomers
DROP TABLE
StatementThe DROP TABLE
statement is used to remove a table from the database. The syntax is
DROP TABLE table-name
Here’s an example:
DROP TABLE tblCustomers
Many people are unaware that the result of a function can serve as an expression in a query or as a parameter to a query. The query shown in Figure 12.38 evaluates the result of a function called Initials
. The return value from the function is evaluated with criteria to determine whether the employee is included in the query result. The Initials
function shown here (it’s also in the basUtils
module of CHAP12EX.ACCDB
, found on the sample code website) receives two strings and returns the first character of each string followed by a period:
Figure 12.38. A query that uses the result of a function as an expression.
The return value from a function can also be used as the criteria for a query (see Figure 12.39). The query in the figure uses a function called HighlyPaid
to determine which records appear in the query result. Here’s what the HighlyPaid
function looks like. (It’s also in the basUtils
module of CHAP12EX.ACCDB
, found on the sample code website.)
Figure 12.39. A query that uses the result of a function as criteria.
The function receives the employee’s title as a parameter. It then evaluates the title and returns a threshold value to the query that’s used as the criterion for the query’s Billing Rate column.
Parameter
Query Values from a FormThe biggest frustration with Parameter
queries occurs when multiple parameters are required to run a query. The user is confronted with multiple dialog boxes, one for each parameter in the query. The following steps explain how to build a Parameter
query that receives its parameter values from a form:
Parameter
query (see Figure 12.40).
Figure 12.40. The Click
event code of the command button that calls the Parameter
query.
Figure 12.42. The Query Parameters dialog box lets you select the data type for each parameter in the query.
Jet 4.0, the version of Jet that ships with Access 2000 and later, includes expanded support for the ANSI-92 standard. The Access Database Engine, included with Access 2007, supports these same features. Although these extensions are not available via the Access user interface, you can tap into them using ADO code. The following sections cover the extensions and the functionality they afford you. Because I have not yet covered ADO, you might want to refer to Chapter 15 to better understand the examples. For now, you need to understand that the code examples in the following sections use the ADO Command
object to execute SQL statements that create and manipulate database objects.
Six table extensions are included with the Access Database Engine. These extensions enable you to
The DEFAULT
keyword can be used with the CREATE TABLE
statement. The syntax is
DEFAULT (value)
Here’s an example:
Notice first that ADO is used to execute the SQL statement. The reason is that the DEFAULT
keyword is not accessible via the use interface. The CreditLimit
field includes a DEFAULT
clause that sets the default value of the field to 5000
.
The CHECK
keyword can be used with the CREATE TABLE
statement. It allows you to add business rules for a table. Unlike field- and table-level validation rules that are available via the user interface, check constraints can span tables. The syntax for a check constraint is
[CONSTRAINT [name]] CHECK (search_condition)
Here’s an example:
This example creates a check constraint on the IntroDate
field that limits the value entered in the field to a date on or before today’s date.
The ANSI-92 extensions can also be used to establish cascading referential integrity. The syntax is
CONSTRAINT name FOREIGN KEY (column1 [,column2 [,...]])
REFERENCES foreign-table [(foreign-column1 [, foreign-column2 [,...]])]
[ON UPDATE {NO ACTION|CASCADE}]
[ON DELETE {NO ACTION|CASCADE}]
Without the CASCADE
options, the primary key field cannot be updated if the row has child records, and the row on the “one” side of the one-to-many relationship cannot be deleted if it has children.
Whenever you join two tables in a one-to-many relationship, Access automatically creates an index on the foreign key field (the “many” side of the relationship). This is generally a good thing. It is bad only if the foreign key contains a lot of Null
s. In that case, the index serves only to degrade performance rather than improve it. Fortunately, using the Jet 4.0 or the Access Database Engine ANSI-92 extensions and the NO INDEX
keywords, you can create the foreign key without the index. Here’s the syntax:
CONSTRAINT name FOREIGN KEY NO INDEX (column1 [,column2 [,...]])
REFERENCES foreign-table [(foreign-column1 [, foreign-column2 [,...]])]
[ON UPDATE {NO ACTION|CASCADE}]
[ON DELETE {NO ACTION|CASCADE}]
Just as you can implement Unicode string compression using the user interface, you can also implement it in code. The syntax is
Column string-data-type [(length)] WITH COMPRESSION
Using the Jet 4.0 or the Microsoft Access Database ANSI-92 extensions, you can change both the autonumber seed and increment. The syntax is
Column AUTOINCREMENT (seed, increment)
Here’s an example:
The code creates an auto-increment field called CustomerID
. The starting value is 100000
. The field increments by 1. In addition to the added support for seed value and increment value, the Jet 4.0 or Access Database Engine ANSI-92 extensions allow you to retrieve the last-assigned autonumber value. Here’s how it works:
The code first inserts a row into the tblCustomers
table. It then opens a recordset and retrieves the @@Identity
value. As with SQL Server, this @@Identity
variable contains the value of the last assigned autonumber.
The Jet 4.0 or Access Database Engine ANSI-92 extensions allow you to create views and stored procedures similar to those found in SQL Server. Essentially, these views and stored procedures are Access queries that are repackaged to behave like their SQL Server counterparts. Although stored as queries, the views and stored procedures that you create are not visible via the user interface. You can execute them just like saved queries. The syntax to create a view looks like this:
CREATE VIEW view-name [(field1 [(,field2 [,...]])] AS select-statement
Here’s an example:
As covered in Chapter 15, use the following code to execute the view:
The syntax to create a stored procedure is
CREATE PROC[EDURE] procedure [(param1 datatype1 [,param2 datatype2 [,...]])]_
AS sql-statement
Here’s an example:
Use the EXECUTE
statement, as shown in the following code, to execute the stored procedure:
Using Jet 4.0 or Access Database Engine ANSI-92 security extensions, you can create transactions that span an ADO connection. These extensions are intended to augment, rather than replace, ADO transactions. You use BEGIN TRANSACTION
to start a transaction, COMMIT TRANSACTION
to commit a transaction, and ROLLBACK [TRANSACTION]
to cancel a transaction. Transactions are covered in detail in Alison Balter’s Mastering Access 2002 Enterprise Development.
The following sections provide several practical applications of the advanced techniques learned in this chapter.
The examples shown in the following sections are included in the CHAP12EX.ACCDB
database on the sample code website.
After a while, you might need to archive some of the data in the tblPayment
table. Two queries archive the payment data. The first, called qappAppendToPaymentArchive
, is an append query that sends all data in a specified date range to an archive table called tblPaymentsArchive
(see Figure 12.43). The second query, called qdelRemoveFromPayments
, is a delete query that deletes all the data archived from the tblPayments
table (see Figure 12.44). The archiving is run from a form called frmArchivePayments
, where the date range can be specified by the user at runtime (see Figure 12.45).
Figure 12.43. The append query qappAppendToPaymentArchive
.
Figure 12.44. The delete query qdelRemoveFromPayments
.
Figure 12.45. The form that supplies criteria for the archive process.
At times, you might want to combine data from both tables. To do this, you’ll need to create a union query that joins tblPayments
to tblPaymentsArchive
. The query’s design is shown in Figure 12.46.
Figure 12.46. Using a union query to join tblPayments
to tblPaymentsArchive
.
Because you’ll regularly be looking up the states and provinces, you need to build a unique list of all the states and provinces in which your clients are currently located. The query needed to do this is shown in Figure 12.47. The query uses the tblClients
table to come up with all the unique values for the StateProvince
field. Here, you use a make table query that takes the unique list of values and outputs it to a tblStateProvince
table.
Figure 12.47. A make table query that creates a tblStateProvince
table.
As you can see, Microsoft gives you a sophisticated query builder for constructing complex and powerful queries. Action queries let you modify table data without writing code; you can use these queries to add, edit, or delete table data. The Unique Values
and Top Values
properties of a query offer you flexibility in determining exactly what data is returned in your query result.
You can do many things to improve your queries’ efficiency. A little attention to the details covered in this chapter can give you dramatic improvements in your application’s performance.
Other special types of queries covered in this chapter include crosstab queries, outer joins, and self-joins. Whatever you can’t do by using the graphical QBE grid, you can accomplish by typing the required SQL statement directly into the SQL View window. In this window, you can type Access SQL statements or use SQL Pass-Through to type SQL statements in the SQL dialect that’s specific to your back-end database. After you harness the power of the SQL language, you can perform powerful tasks such as modifying the record source of a form or report at runtime.
3.15.231.194