| Click here to support our advertisers

SOFTWARE FOR SALE
BOOKS FOR SALE
SEARCH CENTRAL
JOB BANK
CLASSIFIEDS
DIRECTORIES
REFERENCE Online Library
LEARNING CENTER
JOURNAL
NEWS CENTRAL
DOWNLOADS
COMMUNITY
CALENDAR
ABOUT US
Journal:
Get the weekly email highlights from the most popular journal for developers! Current issue
> developer.com developerdirect.com htmlgoodies.com javagoodies.com jars.com intranetjournal.com javascripts.com | All Categories : C/C++ vcg08.htm Using Crosstab Queries to Present Summary Data Summary Data Created by 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 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 # - 8 - Running Crosstab and Action Queries Up to this point in the book, you've been introduced to only the most basic SQL queries: SELECT queries. This chapter describes the five additional types of queries that you can execute with SQL's data manipulation language: crosstab, append, update, delete, and make-table queries. 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 datasource 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, which update the data in existing tables and create new tables. Action queries can substitute for lengthy blocks of Visual C++ code when you need to modify the content of tables in your database or when 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 VisiCalc spreadsheet application, managers were accustomed to viewing summary data in the row-column format of accountants' worksheets. Worksheets that are used for comparative performance analysis fall into two basic categories: Time-series format implies that column titles contain date intervals, such as months, quarters, or years, and that row titles designate the category of data being compared. The underlying detail data is grouped by both data category and time period. Time-series summary data often is used to prepare line graphs or bar charts with sales as the y-axis (vertical axis or abscissa) and time as the x-axis (horizontal axis or ordinate). Classification format uses column titles with the names of individuals, regions, divisions, or other organizational categories and data categories for the row titles. This format is restricted to a single, predetermined time period. (Multiple time periods can be represented by "stacking" worksheets with an identical format that can be consolidated by adding the values of corresponding cells.) The most common graphical representation of data from classification worksheets is the pie chart. 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 that is 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," demonstrated that most spreadsheet data formats violate the "no repeating groups" rule of relational tables. Conventional ANSI SQL statements return data in relational format, not spreadsheet format. Thus, a substantial amount of data manipulation ordinarily is required to create a time-series or classification spreadsheet from relational data. In fact, the "denormalization" process is almost as complex as that described in Chapter 4 for normalizing spreadsheet data to relational form. The following sections describe how summary data is returned by ANSI SQL and how you use Access SQL's TRANSFORM and PIVOT statements to automate the denormalization of relational data to spreadsheet format. #### Summary Data Created by the GROUP BY Clause Figure 8.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 employed to create the query return set, shown in the Crosstab query window of Figure 8.1, are Categories, Products, Orders, and Order Details. These tables' values are derived from the Northwind Traders sample database that accompanies Access. The query return set totals orders that were received for each of eight categories of products for the four calendar quarters of 1996. Thus, the grid contains eight records with a Category Name field and four quarter fields. Figure 8.1. Using two GROUP BY criteria to sum the values of orders by category and date. NOTE Figure 8.1 is a composite. Access 95 won't show both a query SQL statement and the result of the query at the same time. You can toggle between the two views using Access 95's View menu when a query is open. NOTE The Access 95 CROSSTAB 95.MDB database (located in the CHAPTR08 subdirectory on the CD that comes with this book) is used for all the examples in this chapter. CROSSTAB 95.MDB contains records for the Categories, Products, and Employees tables that are the same as those in NorthWind.MDB (from Access), except that unnecessary fields have been deleted. The order dates have been updated. Records for years other than 1995 and 1996 have been deleted from the Orders and Order Details tables. Access 95 was used to perform all the sample queries in this chapter. The query that was used to create the data shown in Figure 8.1 is rather lengthy and deserves further explanation. The following list describes each element of the Access SQL statement that appears in Figure 8.1: The three AS predicates of the statement
>     SELECT Categories.[Category ID] AS Cat, Format([Order Date],"yyyy\Qq")

>     AS Period, Format(SUM([Order Details].[Unit Price]*[Order Details].[Quantity]),

>     "$#,##0.00") AS Sales
> alias the Category ID field as Cat and the two formatted fields as Period and Sales, respectively. 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 a format identifier character. The SUM() SQL aggregate function totals the product of the Unit Price and Quantity fields of the Order Details table, which is 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 isn't required in Access 1.x applications because Currency values are automatically formatted with "#,##0.00". Visual C++ bound controls and the grid control don't automatically format the Currency data type. FROM Categories, Products, Orders, [Order Details] identifies the tables in use. The trailing comma separates this statement from the three join statements that follow. Categories INNER JOIN Products ON Categories.[Category ID] = Products.[Category ID] creates the equi-join needed to identify products by category, because the Order Details table contains a Product ID field but not a Category ID field. (You could eliminate this join by substituting Products.[Category ID] for Categories.[Category ID] in the SELECT statement. The join is included in the example because you might want to use the Category Name field rather than the Category ID field for the row titles.) Products INNER JOIN [Order Details] ON Products.[Product ID] = [Order Details].[Product ID] creates the equi-join needed to join the Order Details table to the Products table so that the values in the Order Details table for each product category are associated with the Category ID value. Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID] creates a join so that you can use the following WHERE clause to limit to a range of dates the records of Order Details that are totaled for each category. The WHERE Orders.[Order Date] Between #01/1/95# And #12/31/96# criterion limits the summation to records for the years 1995 and 1996. The WHERE clause in Figure 8.1 is enclosed in double parentheses, which are not necessary. The GROUP BY Categories.[Category ID], Format([Order Date],"yyyy\Qq") clause results in data being grouped by product category and by the quarter of the year. The sequence of the grouping isn't significant in this query. It's 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, isn't necessary to achieve the proper grouping. It's clear from the rows returned by the preceding query and illustrated in Figure 8.1 that a substantial rearrangement of worksheet cells is required if you import this data into a worksheet that is intended to display data in time-series format. You can achieve a result that is 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 and also illustrates the use of the scalar ODBC date function MONTH() in SQL statements that are processed by ODBC drivers:
>     SELECT Categories."Category ID" AS Cat,

>         {fn MONTH(Orders."Order Date")} AS Month,

>         Sum("Order Details"."Unit Price" * "Order Details".Quantity) AS Sales

>         FROM Categories, "Order Details", Orders, Products

>         WHERE Products."Category ID" = Categories."Category ID"

>         AND "Order Details"."Order ID" = Orders."Order ID"

>         AND "Order Details"."Product ID" = Products."Product ID"

>         GROUP BY Categories."Category ID", {fn MONTH(Orders."Order Date")}
> Figure 8.2 shows the SQL window of MS Query displaying the preceding SQL statement and 13 of the last records of the query result table created by executing the statement. Figure 8.2. An ANSI/ODBC SQL statement that summarizes category sales by month. You can use MS Query's conventional SQL processing method, in which you write the SQL statement in MS Query's SQL window or choose File | Execute SQL from MS Query to send an SQL statement directly to the ODBC driver using SQL pass-through. Figure 8.3 shows MS Query's Execute SQL dialog box, ready to execute the SQL statement in Figure 8.2. Figure 8.3. MS Query's Execute SQL window, which is used to implement SQL pass-through. The rows that return when you specify monthly data require even more rearrangement than the quarterly data returned from the Access query. NOTE MS Query can't represent the SQL statement graphically and therefore will give you an error message. The query will be successful regardless. ### Using TRANSFORM and PIVOT to Create Crosstab Queries Access SQL's crosstab query feature solves the data cell rearrangement problem. Two Access SQL keywords, TRANSFORM and PIVOT, handle the denormalization of the data. The elements of an Access 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 data's appearance. 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 table 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 don't need to 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 of the two examples in the preceding section. Figure 8.4 shows the initial syntax of the Access SQL statement that creates the crosstab query return set shown in Figure 8.5. The statement in the SQL window of Figure 8.4 consists of the SQL statement illustrated in Figure 8.1, plus the necessary modifications to make the statement conform to the rules of crosstab queries given in the preceding list. Figure 8.4. Executing a crosstab query with Microsoft Access. NOTE Figure 8.4 is a composite. Access 95 won't show both a query SQL statement and the result of the query at the same time. You can toggle between the two views using Access 95's View menu when a query is open. Figure 8.5. The crosstab query result set from the SQL statement in Figure 8.4. The CROSSTAB 95.MDB database includes a QueryDef object, qryCategoryByMonth (Crosstab), that creates the query result set shown in Figure 8.5. Select the query tab in the Database window in Access. When a query is open, the query toolbar is displayed. The first three buttons let you change from the Design, SQL, and Datasheet view modes. You can then display the SQL statement. After that, click the Datasheet view toolbar button to execute the query. When you execute the crosstab query shown in Figure 8.4, the Access database engine's query optimizer modifies the FROM and INNER JOIN statements as follows:
>     TRANSFORM Format(Sum([Order Details].[Unit Price] *

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

>         SELECT Categories.[Category Name]

>         FROM (Categories INNER JOIN Products ON Categories.[Category ID] =

>         Products.[Category ID])

>         INNER JOIN (Orders INNER JOIN [Order Details]

>         ON Orders.[Order ID] = [Order Details].[Order ID])

>         ON Products.[Product ID] = [Order Details].[Product ID]

>         WHERE ((Orders.[Order Date] Between #01/1/91# And #12/31/91#))

>         GROUP BY Categories.[Category Name]

>         ORDER BY Categories.[Category Name]

>         PIVOT Format([Order Date],"yyyy\Qq");
> The preceding syntax example, which substitutes nested INNER JOIN syntax for individual INNER JOIN statements, more closely follows the SQL-92 syntax for JOIN statements. Each of the tables specified in the original FROM clause appears in the INNER JOIN statements, so it's not necessary to provide a separate table_names list. The first INNER JOIN of the FROM statement uses conventional INNER JOIN syntax. The succeeding nested INNER JOIN causes a join to be created between the result of the first INNER JOIN and the criterion of the last ON statement. The second and third INNER JOINs use the second ON criterion. Nested INNER JOIN statements 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].[Unit Price] *

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

>         SELECT Categories.[Category Name]

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

>         WHERE Products.[Category ID] = Categories.[Category ID]

>         AND [Order Details].[Order ID] = Orders.[Order ID]

>         AND [Order Details].[Product ID] = Products.[Product ID]

>         AND Orders.[Order Date] Between #01/1/91# And #12/31/91#

>         GROUP BY Categories.[Category Name]

>         ORDER BY Categories.[Category Name]

>         PIVOT Format([Order Date],"yyyy\Qq");
> TIP If you use Access to create and test the text for SQL statements to generate crosstab queries, you'll find that Visual C++ executes Access 1.x crosstab query syntax without generating an error. However, your code will be simpler and more readable if you use the WHERE clause 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([Order Date],"yyyy\Qq") to PIVOT Format([Order Date],"mmm yy") to create a monthly rather than a quarterly crosstab query, the column titles appear in the strange sequence shown in Figure 8.6. The PIVOT statement automatically orders the columns alphabetically. Figure 8.6. Monthly column headings sorted alphabetically. You could solve the column sequence problem by changing the PIVOT statement to PIVOT Format([Order Date],"mm/yy") to provide column titles 01/95 through 12/96, but many people prefer three-letter abbreviations for months. Access SQL lets you use the IN() predicate to create fixed column names that appear in any sequence you specify. The following example demonstrates a PIVOT...IN statement that creates monthly column titles for any year:
>     PIVOT Format([Order Date],"mmm")

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

>         "Oct", "Nov", "Dec");
> CAUTION You can't use the IN() predicate with SQL statements that use the full INNER JOIN syntax of Access, as shown in Figure 8.4. If you attempt to execute such a query, you receive the error message Can't repeat field_name in FROM statement. The preceding PIVOT statement gives the more satisfactory result shown in Figure 8.7. Figure 8.7. The query from Figure 8.6 with fixed column heads. The only restriction on the values of the IN() predicate's arguments is that, except for case, each value must exactly match the values returned by the formatted PIVOT statement. If you misspell an argument value, you will find that the data rows of the column with the mistaken header are empty. NOTE The Access 1.x documentation states that crosstab queries with fixed column heads execute faster than queries without the IN() predicate. In practice, the difference in execution speed is difficult to measure. Using the IN() predicate doesn't result in a demonstrable improvement in the performance of the preceding query when executed against the CROSSTAB 95.MDB or NorthWind.MDB database. #### 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 period of time. 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 1991 as the data cell values:
>     TRANSFORM Format(Sum([Order Details].[Unit Price] *

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

>         SELECT Categories.[Category Name]

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

>         WHERE Products.[Category ID] = Categories.[Category ID]

>         AND [Order Details].[Order ID] = Orders.[Order ID]

>         AND [Order Details].[Product ID] = Products.[Product ID]

>         AND Orders.[Employee ID] = Employees.[Employee ID]

>         AND Orders.[Order Date] Between #01/1/91# And #12/31/91#

>         GROUP BY Categories.[Category Name]

>         ORDER BY Categories.[Category Name]

>         PIVOT Employees.[Last Name];
> Figure 8.8 shows the crosstab query result set returned by the preceding SQL statement that is executed in Microsoft Access. Figure 8.8. A classification crosstab query executed in Access. ### Defining Action Queries Action queries provide a method of creating or modifying persistent database objects without the necessity of writing low-level Visual C++ 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. ### Using the SQL Action Queries The following sections give examples of the use of each of the four types of action queries that Access SQL supports. All of the following examples use the CROSSTAB 95.MDB database opened in Microsoft Access. #### Creating New Tables with Make-Table Queries To avoid modifying the existing tables in the CROSSTAB 95.MDB database, the first action query example creates a new table from the Orders table, tblOrders, that includes all orders that have order dates earlier than July 1, 1991. To test make-table action query syntax, enter the following in Access's Query SQL Statement window:
>     SELECT *, [Order Date]

>         INTO tblOrders

>         FROM Orders

>         WHERE [Order Date]<#07/1/91#
> You need to include the [Order Date] entry in the SELECT list because Access SQL doesn't let you specify a WHERE criterion on a field that isn't explicitly declared in the SELECT list. NOTE The NewOrders table doesn't appear in the Tables tab of Microsoft Access until you execute the query after saving it. After the query is executed (Access will warn you that a new table will be created), you can view the new table. Use a make-table query to create a backup of a table before you test 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 the records in the original table and appending all the records from the backup table. You can add an IN clause to your make-table query to create a table in another database. Here's the full syntax of an SQL make-table clause:
>     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:\ACCESS\SAMPAPPS\NorthWind.MDB database (if you have the Northwind Traders database installed) with the following statement:
>     SELECT *, [Order Date]

>         INTO tblOrders IN "c:\access\nwind.mdb"

>         FROM Orders

>         WHERE [Order Date]<#07/1/91#
> #### Appending Records to Existing Tables You can append the balance of the records for the year 1991 to the tblOrders table with the following append query statement:
>     INSERT INTO tblOrders ([Order ID], [Customer ID], [Employee ID],

>         [Order Date], [Shipped Date], [Order Amount])

>         SELECT [Order ID], [Customer ID], [Employee ID], [Order Date],

>         [Shipped Date], [Order Amount]

>         FROM Orders

>         WHERE [Order Date] Between #07/1/91# And #12/31/91#
> 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 in parentheses. If you attempt to use the asterisk (*) to add all fields and then add the [Order Date] 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 reduces the dollar amount of each order received in April 1991 to 90 percent of its current value:
>     UPDATE tblOrders

>         SET [Order Amount] = 0.9*[Order Amount]

>         WHERE [Order Date] BETWEEN #4/1/91# AND #4/30/91#;
> If you want to update the values of more than one field, add the field name, the equals sign (=), and an expression separated from the preceding SET expression by 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 1991 from the tblOrders table, use the following statement:
>     DELETE FROM tblOrders

>         WHERE [Order Date] BETWEEN #7/1/91# AND #12/31/91#;
You now can safely verify that the DELETE FROM tblOrders statement without the WHERE clause does indeed delete all the records in the table. ### Summary This chapter demonstrated how to use the Access SQL TRANSFORM and PIVOT keywords to create crosstab queries and showed 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 C++ decision-support applications. The next chapter makes more extensive use of crosstab queries. Using action queries to create and update tables often can save you from writing a substantial amount of Visual C++ code. This chapter ends Part II of this book. At this point, you have the background you need in database design and the use of both ANSI and Access SQL to develop meaningful database applications. Part III shows you how to design applications to display and print the information that you extract from databases with SQL SELECT queries. Contact reference@earthweb.com with questions or comments. Copyright 1998 EarthWeb Inc., All rights reserved. PLEASE READ THE ACCEPTABLE USAGE STATEMENT. Copyright 1998 Macmillan Computer Publishing. All rights reserved. |