• Using Crosstab Queries to Present Summary Data
  • Summary Data Created with the GROUP BY Clause
• Using TRANSFORM and PIVOT to Create Crosstab Queries
  • Creating Fixed Column Headers with the IN Predicate
  • Creating a Classification Crosstab Query
• Defining Action Queries
  • Executing Action Queries with Visual Basic 4.0 Code
  • Equivalents of Action Queries in Visual Basic 4.0 Code
  • Maintaining Referential Integrity when Executing Action Queries
• Using the SQL Action Queries
  • Creating New Tables with Make-Table Queries
  • Appending Records to Existing Tables
  • Updating Data Values in Tables
  • Deleting Records from Tables
• Summary

- 7 -
Running Crosstab and Action Queries

Up to this point in Database Developer's Guide with Visual Basic 4, you've only been introduced to the most basic SQL queries: SELECT queries. This chapter describes the five additional types of queries—crosstab, append, update, delete, and make-table—you can execute with SQL's data manipulation language. The crosstab query is a special form of SELECT query that summarizes data in spreadsheet style, most often in time-series format. You are likely to find that 75 percent or more of the decision-support applications you create for your firm or clients include crosstab queries. In many cases, the crosstab query result set serves as the data source for graphs and charts.

The remaining four types of queries described in this chapter are called action queries. Microsoft Access introduced the action query category for manipulative (Data Manipulation Language or DML) queries that update the data in existing tables and create new tables. Action queries can substitute for lengthy blocks of Visual Basic code when you need to modify the content of tables in your database or you want to create a new database table.

Using Crosstab Queries to Present Summary Data

Even before the early days of the personal computer and the historic VisiCalc spreadsheet application, management was accustomed to viewing summary data in the row/column format of accountant worksheets. Worksheets that are used for comparative performance analysis fall into two basic categories:

Today's spreadsheet applications, such as Lotus 1-2-3 and Microsoft Excel, replace the drudgery of preparing handwritten worksheets with automated computer-based procedures. However, the majority of the detail information needed to prepare summary data for management is available only in the fields of tables of relational accounting databases. Chapter 4, "Optimizing the Design of Relational Databases," demonstrates that most spreadsheet data formats violate the "no repeating groups" rule of relational tables. Conventional ANSI SQL statements return data in relational form, not in spreadsheet format. Therefore, a substantial amount of data manipulation is ordinarily required to create a time-series or classification spreadsheet from relational data. In fact, the denormalization process is almost as complex as the process described in Chapter 4 for normalizing spreadsheet data to relational form.

The sections that follow describe how summary data is returned by ANSI SQL and how you use Jet SQL's TRANSFORM and PIVOT statements to automate the denormalization of relational data to spreadsheet format.

Summary Data Created with the GROUP BY Clause

Figure 7.1 illustrates the format of data returned by conventional SELECT queries that use the SQL aggregate function SUM() to prepare data from which you can create a time-series worksheet. The tables used to create the query result shown in the left SQL window of Figure 7.1 are the Categories, Products, Orders, and Order Details tables; their values are derived from the Northwind Traders sample database that accompanies Access 95. The query result set totals the orders received for each of the eight categories of products for the four calendar quarters of 1994. Thus, the grid contains 8 * 4 (or 32) records.

---

The Crosstab.mdb database is included on the accompanying CD-ROM and is located in the \Ddg_vb4\32_bit\Chaptr07 directory. It is used for all the examples in this chapter. Crosstab.mdb, a Jet 3.0 database, contains records for the Categories, Products, and Employees tables, which are the same as those in the Access 95 Northwind.mdb sample database file, except that unnecessary fields have been deleted, some lookup fields have been changed to regular fields, and some column captions (aliases) have been removed. Records for years other than 1994 have been deleted from the Orders and Order Details tables.

---

Figure 7.1. Using two GROUP BY criteria to sum the values of orders by category and date.

The query used to create the Dynaset-type Recordset data shown in Figure 7.1 is rather lengthy and deserves further explanation. The following list describes each element of the Jet SQL statement that appears in Figure 7.1:

• The three AS predicates of the following statement alias the CategoryID field as Cat and the two formatted fields as Period and Sales, respectively:
  
  

SELECT Categories.[CategoryID] AS Cat, 



Format([OrderDate],"yyyy\Qq") AS Period, 



Format(Sum([Order Details].[UnitPrice]*



   [Order Details].[Quantity]),"$#,##0.00") AS Sales

• The first Format() function causes the order date to appear as the year (yyyy), followed by a literal "Q" (\Q) and the number of the quarter (q) that corresponds to the order date. A backslash preceding a character in a format string designates the character that follows as a literal character rather than as a format identifier character.
  
  
• The SUM() SQL aggregate function totals the product of the UnitPrice and Quantity fields of the Order Details table; these fields are needed to compute the extended amount of each invoice line item.
  
  
• The Format() function embedded within the SUM() function adds the dollar sign ($), comma thousands separator (#,##0), and trailing cents columns (.00) to the values in the Sales column. This function would not be required in Access applications because Currency values are automatically formatted with #,##0.00. Visual Basic bound controls and the grid control do not automatically format the Currency data type.
  
  
• The following clause identifies the tables from which data is selected:
  
  

FROM Categories INNER JOIN (Products INNER JOIN (Orders



INNER JOIN [Order Details] ON Orders.[OrderID] = [Order Details].[OrderID])



ON Products.[ProductID] = [Order Details].[ProductID]) 



ON Categories.[CategoryID] = Products.[CategoryID]

The FROM statement lists the first table, Categories, and then specifies how the remaining tables in the query are joined to it. Notice that the INNER JOIN clauses are nested so that they include a total of four different tables: the Categories table is joined to the Products table, the Products table is in turn joined to the Orders table, and the Orders table is joined to the Order Details table.


• The innermost join (the INNER JOIN enclosed in the deepest level of parentheses in the following statement) creates an equi-join between the Orders table and the Order Details table so that you can use a WHERE clause to limit the records from Order Details that are totaled for each category to a range of dates. Here's the join:
  
  

Orders INNER JOIN [Order Details] 



ON Orders.[OrderID] = [Order Details].[OrderID]

• The next innermost level of join creates the needed equi-join between the Order Details table and the Products table so that the values in the Order Details table for each product category are associated with the CategoryID value. Here's the join:
  
  

Products INNER JOIN (Orders INNER JOIN [Order Details] 



ON Orders.[OrderID] = [Order Details].[OrderID]) 



ON Products.[ProductID] = [Order Details].[ProductID]

• The outermost join creates an equi-join between the Categories table and the result of the two nested INNER JOIN clauses. This join is needed to identify products by Category, because the Order Details table contains a ProductID field, but not a CategoryID field. (This join could be eliminated by substituting Products.[CategoryID] for Categories.[CategoryID] in the SELECT statement. The join is included in the example because you might want to use the CategoryName field, rather than the CategoryID field, for the row titles.)
  
  

Categories INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order Details]



ON Orders.[OrderID] = [Order Details].[OrderID]) 



ON Products.[ProductID] = [Order Details].[ProductID]) 



ON Categories.[CategoryID] = Products.[CategoryID]

• The WHERE Orders.[OrderDate] Between #01/1/94# And #12/31/94# criterion limits the summation to records for the year 1994. The WHERE clause in Figure 7.1 is enclosed between double parentheses, which are not necessary.
  
  
• The GROUP BY Categories.[CategoryID], Format([OrderDate],"yyyy\Qq") clause results in data being grouped by product category and by the quarter of the year. The sequence of the grouping is not significant in this query. It is customary to use the same Format() function in the GROUP BY clause as is used in the SELECT statement that displays the grouping value. The literal "\Q", however, is not necessary to achieve the proper grouping.
  
  

It's clear from the rows returned by the preceding query and illustrated in Figure 7.1 that a substantial rearrangement of worksheet cells is required if you import this data into a worksheet intended for displaying data in time-series format.

You can achieve a result similar to the preceding example with ANSI/ODBC SQL using Microsoft Query. The following ANSI/ODBC SQL statement returns sales for each of the eight categories by month in 96 rows. It also illustrates the use of the scalar ODBC date function, MONTH(), in SQL statements processed by ODBC drivers:

SELECT Categories.CategoryID AS Cat,



   {fn MONTH(Orders.OrderDate)} AS Month,



   Sum("Order Details".UnitPrice * "Order Details".Quantity)



   AS Sales



   FROM Categories, "Order Details", Orders, Products



   WHERE Products.CategoryID = Categories.CategoryID



     AND "Order Details".OrderID = Orders.OrderID



     AND "Order Details".ProductID = Products.ProductID



   GROUP BY Categories.CategoryID, {fn MONTH(Orders.OrderDate)}

Figure 7.2 shows the SQL window of MS Query displaying the preceding SQL statement and the last 22 records of the query result table created by executing the statement.

Figure 7.2. An ANSI/ODBC SQL statement that summarizes category sales by month.

You can use MS Query's conventional SQL processing method, wherein you write the SQL statement in MS Query's SQL window or choose MS Query's File | Execute command to send an SQL statement directly to the ODBC driver using SQL passthrough. Figure 7.3 shows MS Query's Execute SQL dialog, ready to execute the SQL statement of Figure 7.2.

Figure 7.3. Microsoft Query's Execute SQL window, used to implement SQL passthrough.

The 96 rows that return when you specify monthly data require even more rearrangement than the quarterly data returned from the VisData query (see Figure 7.1).

Using TRANSFORM and PIVOT to Create Crosstab Queries

Jet SQL's crosstab query feature solves the data cell rearrangement problem. Two Jet SQL keywords, TRANSFORM and PIVOT, handle the denormalization of the data. The elements of a Jet SQL crosstab query are as follows:

• The object of the TRANSFORM predicate specifies the values for the data cells and must contain one of the SQL aggregate functions, such as SUM() or AVG(). You use the Format() function to determine the appearance of the data.
  
  
• The SELECT statement designates the row titles. The field you specify with the SELECT statement must be the same as the GROUP BY field.
  
  
• The FROM statement specifies each of the tables involved in the query. The tables may be identified by the INNER JOIN statements, without the conventional comma-separated table_names list.
  
  
• The GROUP BY clause aggregates row data. Only one field is permitted in the GROUP BY clause of a conventional crosstab query.
  
  
• The optional ORDER BY clause sorts the rows by the value of the single field specified in the SELECT and GROUP BY statements. You need not add the ORDER BY clause if the field specified by the SELECT statement is a primary key field and you want the rows ordered by the primary key.
  
  
• The PIVOT statement determines the column grouping and supplies the column titles. Column titles consist of the value of the grouping criterion. The object of the PIVOT predicate takes the place of the second GROUP BY field of the SQL statements in the two preceding examples.
  
  

Figure 7.4 shows the initial syntax of the Jet SQL statement that creates the crosstab query return set illustrated in Figure 7.5. The statement in the SQL window of Figure 7.4 consists of the SQL statement illustrated in Figure 7.1, plus the necessary modifications to make the statement conform to the rules of crosstab queries given in the preceding list.

Figure 7.4. Executing a crosstab query with the VisData application.

The Crosstab.mdb database includes a QueryDef object, qryCategoryByMonth (Crosstab), that creates the query result Recordset of the Snapshot type shown in Figure 7.5. Choose the QueryDefs option button in VisData's Tables/Queries window and then click the Open button to display the SQL statement in the SQL Statement window. Finally, click the Execute button in the SQL window to execute the query.

Figure 7.5. The crosstab query result set from the SQL statement in Figure 7.4.

Nested INNER JOIN statements, like the ones shown in Figure 7.1 and Figure 7.4, are quite difficult to interpret correctly and are even more difficult to write. A simpler approach to specifying inner joins uses a compound WHERE clause. The following WHERE clause syntax achieves the same result as the INNER JOIN syntax of the preceding example:

TRANSFORM Format(Sum([Order Details].[UnitPrice] *



      [Order Details].[Quantity]), "$#,##0") AS Sales



   SELECT Categories.[CategoryName]



      FROM Categories, [Order Details], Orders, Products



      WHERE Products.[CategoryID] = Categories.[CategoryID]



         AND [Order Details].[OrderID] = Orders.[OrderID]



         AND [Order Details].[ProductID] = Products.[ProductID]



         AND Orders.[OrderDate] Between #01/1/94# And #12/31/94#



      GROUP BY Categories.[CategoryName]



      ORDER BY Categories.[CategoryName]



PIVOT Format([OrderDate],"yyyy\Qq");

---

If you use Microsoft Access to create and test the text for SQL statements to generate crosstab queries, you'll find that Visual Basic 4.0 executes Microsoft Access crosstab query syntax without generating an error. However, your code is simpler and more readable if you use the WHERE Table1.Field = Table2.Field clause instead of INNER JOIN syntax to create equi-joins.

---

Creating Fixed Column Headers with the IN Predicate

If you change the PIVOT statement in the preceding example from PIVOT Format([OrderDate],"yyyy\Qq") to PIVOT Format([OrderDate],"mmm yy") in order to create a monthly rather than a quarterly crosstab query, the column titles appear in a strange sequence, illustrated by Figure 7.6. The PIVOT statement automatically orders the columns alphabetically.

Figure 7.6. Monthly column headings sorted alphabetically.

You could solve the column sequence problem by changing the PIVOT statement to PIVOT Format([OrderDate],"mm/yy") to provide column titles 01/94. . .12/94, but many people prefer the three-letter abbreviations for months. Jet SQL enables you to use the IN() predicate to create fixed column names that appear in any sequence you specify. The example that follows demonstrates the PIVOT. . .IN statement to create monthly column titles for any year:

PIVOT Format([OrderDate],"mmm")



   IN ("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug",



      "Sep", "Oct", "Nov", "Dec")

The preceding PIVOT statement gives the more satisfactory result shown in Figure 7.7.

Figure 7.7. The query of Figure 7.6 with corrected column heads.

The only restriction on the values of the arguments of the IN() predicate is that each value must exactly match, except for case, the values returned by the formatted PIVOT statement. If you misspell one of the argument values, the data rows of the column with the mistaken header are empty.

Creating a Classification Crosstab Query

With a few simple changes and additions to the preceding query, you can create a crosstab query in classification format to compare sales of product categories, by employee, during a specific time period. The following SQL statement adds the Employees table to the query and displays the last name of each employee of Northwind Traders as column headings, with sales by category for 1994 as the data cell values:

TRANSFORM Format(Sum([Order Details].[UnitPrice] *



      [Order Details].[Quantity]), "$#,##0") AS Sales



   SELECT Categories.[CategoryName] AS Category



      FROM Categories, [Order Details], Orders, Products,



         Employees



      WHERE Products.[CategoryID] = Categories.[CategoryID]



         AND [Order Details].[OrderID] = Orders.[OrderID]



         AND [Order Details].[ProductID] = Products.[ProductID]



         AND Orders.[EmployeeID] = Employees.[EmployeeID]



         AND Orders.[OrderDate] Between #01/1/94# And #12/31/94#



      GROUP BY Categories.[CategoryName]



      ORDER BY Categories.[CategoryName]



PIVOT Employees.[LastName];

Figure 7.8 shows the crosstab query result set returned by the preceding SQL statement that is executed in the VisData application.

Figure 7.8. A classification crosstab query executed in the VisData application.

Defining Action Queries

Action queries provide a method of creating or modifying persistent database objects without the necessity of writing low-level Visual Basic code to manipulate table record pointers. Action queries are especially effective when you want to add, delete, or modify many records in a database with a single operation. (An operation that makes changes to multiple sets of records is called a bulk update.) The following list briefly describes the four types of action queries:

• Append queries use the INSERT INTO table_name predicate followed by a conventional SELECT statement to specify the fields and criteria used to determine the data to be appended to table_name.
  
  
• Delete queries use the DELETE FROM table_name WHERE criteria syntax to delete records whose data meets the criteria.
  
  
• Update queries use the UPDATE table_name SET field_name = expression statement with a WHERE clause to establish which records are updated.
  
  
• Make-table queries use SELECT field_names INTO dest_table FROM source_table statements to create a new table, dest_table, with fields specified by the field_names list.
  
  

Examples of the use of each of these four SQL statements are presented in separate sections that appear later in this chapter as well as in Chapter 13, "Designing Online Transaction-Processing Systems."

Executing Action Queries with Visual Basic 4.0 Code

Action queries don't select a group of records; as a result, action queries don't return Recordset objects. You use the Visual Basic Execute method to carry out action queries. The differences between the two methods of executing action queries are described in the following two sections.

You use the Execute method when you want the Jet database engine to process the action query. Here's the syntax for the Execute method:

dbName.Execute {qdfName|strSQL}[, intOptions]

You can specify either the name of a QueryDef object or a string consisting of a valid Jet SQL action query statement as the value of the first argument of the Execute statement. Table 7.1 lists the allowable values of the optional intOptions argument, showing the Visual Basic 4.0 constant names, the actual value, and the purpose of that particular option. (You can combine option values by adding them together arithmetically.)

If your action query fails because of referential integrity violations, or if you attempt to use Execute to execute a query that returns rows, you receive a trappable error message. If you specify both the dbInconsistent and the dbConsistent options, or neither option, the default setting is used.

An Execute method applied with a syntactically correct SQL statement never fails. This means that if your action query is syntactically correct but can't change any records because of locked records, no error is generated. For this reason, you should always include the dbFailOnError option in action queries performed on multiuser databases.

Use the RecordsAffected property of the Database or QueryDef object to determine how many records were affected by your action query.

---

The ExecuteSQL method of Visual Basic 3.0 for executing queries against client/server databases is obsolete. Jet 3.0 uses the Execute method for all action queries.

---

Equivalents of Action Queries in Visual Basic 4.0 Code

Each of the preceding types of action SQL statements has corresponding methods in Visual Basic 4.0. Table 7.2 lists the action query type, SQL statement, and the equivalent Visual Basic 4.0 method(s). The rsName object in Table 7.2 must be an updatable Recordset object of the Dynaset or Table type.

Using the Append method to create new tables and add fields to tables is one of the subjects covered in Chapter 12, "Extending Your Use of the Data Access Object."

Maintaining Referential Integrity when Executing Action Queries

When you use append, delete, and update queries, you need to observe the referential integrity rules described in Chapter 4. If you are using a Jet database created in Microsoft Access with default relations you have established with the Relationships dialog box, the Jet database engine enforces referential integrity rules for you. (This assumes that you ticked the Enforce Referential Integrity check box when you established the default relations.)

The Jet database engine enforces referential integrity by applying transaction-processing methods in conjunction with append, delete, and update operations. The Jet engine adds the equivalent of a BEGIN TRANS[ACTION] command before your SQL statement and terminates the statement with a COMMIT TRANS[ACTION] command. If, for example, your delete or append SQL statement would create orphaned records, the Jet database engine would detect this violation of referential integrity and issue the equivalent of a ROLLBACK TRANS[ACTION] command, instead of executing COMMIT TRANS[ACTION]. The transaction commands are based on SQL Server's SQL implementation; equivalent methods in Visual Basic are BeginTrans, CommitTrans, and Rollback.

If you use a database type other than a Jet or a client/server database, it is up to you to enforce referential integrity with Visual Basic code. A simple example of enforcing referential integrity is provided near the end of Chapter 3, "Using Visual Basic's Data Control." Chapter 13 provides in-depth coverage of methods of maintaining both referential and domain integrity with Visual Basic code.

Using the SQL Action Queries

The sections that follow give examples of the use of each of the four types of action queries supported by Jet SQL. All of the following examples use the Crosstab.mdb database opened in the VisData application.

Creating New Tables with Make-Table Queries

To avoid modifying the existing tables in the Crosstab.mdb database, the first action query example creates a new table from the Orders table, tblOrders, which includes all orders that have order dates earlier than July 1, 1994. To test make-table action query syntax, enter the following in VisData's SQL Statement window:

SELECT *, [OrderDate]



   INTO tblOrders



   FROM Orders



   WHERE [OrderDate]<#07/1/94#

You need to include the [OrderDate] entry in the SELECT list because Jet SQL does not enable you to specify a WHERE criterion on a field that is not explicitly declared in the SELECT list.

---

The tblOrders table does not appear in the Tables/Queries window of the VisData application until you right-click any table name in the list and then choose Refresh List from the pop-up shortcut menu. This menu choice applies the Refresh method to the collections of the Crosstab.mdb database. (The Tables viewing option must also be selected in the Tables/Queries window of the VisData application.)

---

Use a make-table query to create a backup of a table prior to testing other SQL action statements on the table. If your append, update, or delete query gives an unexpected result, you can start fresh by deleting all of the records in the original table and appending all of the records from the backup table.

You can add an IN clause to your make-table query to create a table in another database. The full syntax of an SQL make-table clause is this:

SELECT [ALL|DISTINCT|DISTINCTROW] select_list



   INTO dest_table [IN database_name[ connect_string]]



   FROM source_table



   WHERE criteria

Thus, you can create a tblOrders table in the C:\MSOffice\Access\Samples\Northwind.mdb database (if you have the Northwind Traders database installed in the default directory) with the following statement:

SELECT *, [OrderDate]



   INTO tblOrders



      IN "c:\msoffice\access\samples\northwind.mdb"



   FROM Orders



   WHERE [OrderDate]<#07/1/94#

Appending Records to Existing Tables

You can append the balance of the records for the year 1994 to the tblOrders table with the following append query statement:

INSERT INTO tblOrders ([OrderID], [CustomerID], [EmployeeID],



      [OrderDate], [ShippedDate])



   SELECT [OrderID], [CustomerID], [EmployeeID], [OrderDate],



       [ShippedDate]



   FROM Orders



   WHERE [OrderDate] Between #07/1/94# And #12/31/94#

You need to explicitly declare the field list of both the destination table (tblOrders) and the source table (Orders) if you use a WHERE criterion. The field list of the destination table must be enclosed within parentheses. If you attempt to use the asterisk (*) to add all fields and then add the [OrderDate] field to the SELECT statement as in the make-table example, you receive a "syntax error" message.

Updating Data Values in Tables

To change values in data fields, you use the UPDATE predicate. The SET clause specifies the expression used to update one or more fields. The following update action query SQL statement changes the ShippedDate field in the tblOrders table for every order shipped in April 1994 to a date two days later by adding 2 to the existing value of the ShippedDate field:

UPDATE tblOrders



   SET [ShippedDate] = [ShippedDate] + 2



   WHERE [OrderDate] BETWEEN #4/1/94# AND #4/30/94#;

If you want to update the values of more than one field, add the field name, the equal sign (=), and an expression separated from the preceding SET expression with a comma (,).

Deleting Records from Tables

The simplest and most potentially destructive of the action queries is the delete query. If you execute a delete query and forget to add a WHERE criterion, all the records in your table can disappear in an instant. To delete the records for the last half of 1994 from the tblOrders table, use the following statement:

DELETE FROM tblOrders



   WHERE [OrderDate] Between #7/1/94# And #12/31/94#;

You now can safely verify that the DELETE FROM tblOrders statement without the WHERE clause does indeed delete all of the records in the table.

Summary

This chapter demonstrated how to use the Jet SQL TRANSFORM and PIVOT keywords to create crosstab queries and the syntax necessary to implement action queries with SQL statements. Crosstab queries are one of the most commonly used forms of SELECT queries in Visual Basic decision-support applications. More extensive use of crosstab queries is made in the next chapter, "Designing a Decision-Support Front-End," and in Chapter 10, "Graphing Summary Data Created with Crosstab Queries." Using action queries to create and update tables often can save you from writing a substantial amount of Visual Basic code.

Part II of Database Developer's Guide with Visual Basic 4 ends with this chapter. At this point, you have the background you need in database design and the use of both ANSI and Jet SQL to develop meaningful database applications. The chapters in Part III, "An Introduction to Database Front-End Design," show you how to design applications to display and print the information you extract from databases with SQL SELECT queries.