|
|
 |
|
 |
|
|
|
|
Figure 1: Example of a Crosstab Query as Shown in Access 2000 As shown above, a salesperson can sell many categories (beverages, condiments and confections). Likewise, a category of product can be sold by more than one salesperson. The employees are listed as the "Row Heading." The categories are listed as the "Column Heading." The totals are listed as the "Value." You are allowed up to three row headings in a crosstab query, but you are limited to a single column heading and value. Study the relationships of all the joined tables in Figure 2 to determine the fields that you will need to use.
Figure 2: Table Relationships. Note that you should first establish the relationships between your tables to avoid getting a Cartesian product result. A Cartesian product recordset consists of the total number of records selected from each table all multiplied together. This result occurs when relationships have not been established between the tables (or the relationships have been deleted) in the query design. Don't forget to run your query after each step. It will show if you have made a mistake. 1.) Open the Northwind database, select the Queries object in the database window and then click New. Select Design View from the list rather than Crosstab Query Wizard, because we will be building our crosstab query from scratch. (The Crosstab Query Wizard requires that you already have the basic Select query created.) 2.) The Show Table dialog box appears. Select the following five tables: Categories, Products, Order Details, Orders and Employees. 3.) Choose the Fields that will define the row and column headings: LastName from the Employees table and Note: You can add FirstName either as a second row heading, or concatenated into a single row heading, i.e., Employee: LastName & ", " & FirstName 4.) Enter a Calculated Field (Press <SHIFT> + <F2> to open a zoom window) LineItemTotal: ([Order Details!UnitPrice])*[Quantity]*(1-[Discount]) The table name Order Details with an exclamation mark (!) must be included in the calculated field, so that Access will know which UnitPrice to use, since a field with the same name is used in the Products table. Place a check mark in the QBE grid "Show" box to display this result. Notes: UnitPrice should be selected from the Order Details table, rather than the Products table, because this reflects the price at the time the sale was made. Good database design dictates that field names be unique in a database.1 If this design principle had been followed in Northwind, there would be no need to specify the table name in the calculated field. 1 "SQL Queries for Mere Mortals," by Michael J. Hernandez and John L. Viescas, Published by Addison Wesley (Page 21 in the edition published in 2000). 5.) To format this field, right click the calculated Field and click Properties. Under the General tab, click in Format, and in the drop down list, click Currency. As an alternative, you can use the CCur type conversion function to display a currency result. See Access Help for more information on type conversion functions if you are interested. LineItemTotal: CCur (([Order Details!UnitPrice])*[Quantity]*(1-[Discount])) Run the query. You should see 2155 records. Save your work now as qxtbEmployeeSales. Notes: We are using the prefix "qxtb" to denote a crosstab query, even though our query is only a select query at this point. Alternatively, you can save the query as a normal select query, using the prefix "qry," and then use this new query in the Crosstab Query Wizard. However, you will end up with a query that uses another query as its recordsource if you use the Crosstab Query Wizard to finish the process. 6.) Click the Totals button on your toolbar (or use the menu to click on View > Totals). Select Sum in the Group By in the QBE grid for the LineItemTotal field. Run the query. You should see 72 records. 7.) For the Criteria in the Field CategoryName, type: "Beverages" Or "Condiments" Or "Confections" or type: In ("Beverages", "Condiments", "Confections") Run the query. You should see 27 records. Note: Although there are additional categories, our goal at this point is to match the result shown in Figure 1 as closely as possible. Now you are ready to do your crosstab. 8.) In Design View, click Query on the menu bar and then click Crosstab Query. You should see a new row in the QBE grid titled "Crosstab." 9.) Select Row Heading in the Crosstab row under the LastName field. 10.) Select Column Heading in the Crosstab row under CategoryName field. 11.) Select Value in the Crosstab row under the Calculated Field. (You must have a value specified in a crosstab query.) 12.) Run your query. It should look like Figure 3:
Figure 3: Crosstab Query Results. 13.) Save your query one more time. Notes: The results shown above come from a "clean" copy of the Northwind sample database (i.e., no records have been added or deleted). The results shown in Figure 1 indicate higher sales amounts than these results. Apparently, the person who wrote this portion of the Help file was using creative accounting techniques from Enron — they were not using a clean copy of Northwind. 14.) You can add another column that displays a sum for each employee's sales for all categories of product sold. Enter this new column in the QBE grid as indicated below:
15.) Now add any additional criteria that you might want. For example, you could add the OrderDate field from the Orders table, and include a criteria such as:
You may also use a parameter query to allow the user to select the dates. In order to get prompted for parameters in a crosstab query, you must enter the parameter prompts exactly as shown in the criteria line by using the menu to select Query > Parameters...
Valid order dates for the Access 2000 through 2003 versions of the Northwind database include the range between 7/4/1996 to 5/6/1998. If you encounter an error while running the crosstab query with your own defined parameter, please consult the following Microsoft Knowledge Base article: "Error When Running Crosstab Query With A Parameter." For additional information on crosstab queries, you can also read the following Microsoft Knowledge Base article: "HOW TO: Create a Crosstab Query in Microsoft Access 2000." Summary The crosstab query provides an excellent method for displaying the summaries or aggregates of your data, making it easier to identify trends or even recognize inaccurate data. Before creating the crosstab query, make sure that you have already established relationships between the tables to avoid a Cartesian product result. The crosstab query is derived from a select query. If you choose to use the Crosstab Query Wizard to create your crosstab query, then you must create the select query before using the wizard. The crosstab query may contain fields for up to three row headings, but may contain only one field and value for the column heading. In addition to the fields available from the select query, you may also add calculated columns to the crosstab query to further customize your query. By default, the column headings for a crosstab query are sorted in alphabetical or numerical order, depending upon the data type. If you need these column headings to appear in a chronological order (January, February, March, et cetera, for example, and not the default April, August, December, et cetera), or if you need to limit the column headings that will be displayed, then you can use "fixed headings" by setting the "ColumnHeadings" Property in the query's property sheet. However, each time changes need to be made, the settings for these column headings will have to be set again manually. For example, the year-to-date crosstab query for March would contain the column headings "January,February,March" and in April, the column heading "April" would have to be added to the original. In May, the column heading "May" would have to be added, and so on. You can avoid this manual labor by using a "self-adjusting" or dynamically modified query. The dynamically modified crosstab query will be covered in a future article: Dynamically Modify A Crosstab Query.
Created: Jan. 2003 Copyright © 2003-2006 Tom Wickerath. All rights reserved. Reprinted by permission.
You can read about your favorite expert contributors. Visitors since 24 Apr. '04:
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
[MS Access] [Free Stuff] [Articles] [Gem Tips] [How-To Tips] [Links] [Products] [Scorecard] [About Us] [Search] |
|
|
|
|
