You are currently offline, waiting for your internet to reconnect

ACC: Creating a Crosstab Query with Multiple Value Fields

This article was previously published under Q109939
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Novice: Requires knowledge of the user interface on single-user computers.

SUMMARY
In a Microsoft Access crosstab query, you can specify only one fieldor calculation as the value. Often, you may want to show more thanone value in the query.

For example, the following example shows two columns of information percompany, a count of the number of orders, and the order totals for eachyear:
   Company Name  1989 Orders  1989 Total  1990 Orders  1990 Total   --------------------------------------------------------------   ABC Company   12           $855.00     15           $1010.25   XYZ Company   1017         $22045.57   1050         $25345.29				
This type of query is sometimes called a Multiple Value Field query.

NOTE: A demonstration of the technique used in this article can be seenin the sample file, Qrysmp97.exe. For information about how to obtainthis sample file, please see the following article in the MicrosoftKnowledge Base:
182568 ACC97: Microsoft Access 97 Sample Queries Available in Download Center
MORE INFORMATION
In order to create a Multiple Value Fields crosstab query, you mustcreate two separate crosstab queries with fixed column headings for eachvalue you want to calculate. These two queries can then be joined,using a Select query to display the desired results.

The following example uses the sample database Northwind.mdb in MicrosoftAccess version 7.0 and Microsoft Access 97 (or NWIND.MDB in versions 2.0 orearlier) to create a query that displays results similar to theexample above, showing the number of sales and total for each year for eachcompany.

Step One: Create the Order Total Crosstab Query

To create the Order Total Crosstab query, follow these steps:
  1. Open the sample database Northwind.mdb (or NWIND.MDB in version 2.0 or earlier) and create the following crosstab query based on the Orders,Order Details, and the Customers tables.
          Query: Order Total      ------------------      Type: Crosstab Query      Join: Customers.[CustomerID] <-> Orders.[CustomerID]      NOTE: In versions 1.x and 2.0, there is a space in Customer ID.      Join: Orders.[OrderID] <-> Order Details.[OrderID]      NOTE: In versions 1.x and 2.0, there is a space in Order ID.      Field: CompanyName         Table Name: Customers         Total: Group By         Crosstab: Row Heading      NOTE: In versions 1.x and 2.0, there is a space in Company Name.      Field: Expr1: Year([OrderDate]) & " " & "Order Total"         Table Name:         Total: Group By         Crosstab: Column Heading      NOTE: In versions 1.x and 2.0, there is a space in Order Date.      Field: Expr2: Sum(CCur([UnitPrice]*[Quantity]*(1.-[Discount])))         Table Name:         Total: Expression         Crosstab: Value      NOTE: In versions 1.x and 2.0, there is a space in Unit Price.					
  2. Select the query by clicking anywhere on the gray area in the top half of the query grid. Click Query Properties on the View menu, select the Fixed Column Headings check box, and in the Fixed Column Headings box type the entries appropriate for your version of Microsoft Access:
          Version 1.x:      "1989 Order Total";"1990 Order Total";"1991 Order                        Total";"1992 Order Total"      Version 2.0:      "1991 Order Total";"1992 Order Total";"1993 Order                        Total";"1994 Order Total"      Version 7.0:      "1993 Order Total";"1994 Order Total";"1995 Order                        Total"      Version 97:       "1994 Order Total";"1995 Order Total";"1996 Order                        Total"					
NOTE: In Microsoft Access versions 2.0, 7.0, and 97, theFixedColumnHeadings property is called Column Headings.

Step Two: Create the Order Count Crosstab Query

  1. Create the following Crosstab query based on the Orders and the Customers tables:
          Query: Order Count      ------------------      Type: Crosstab Query      Join: Customers.[CustomerID] <-> Orders.[CustomerID]      NOTE: In versions 1.x and 2.0, there is a space in Customer ID.      Field: CompanyName         Table Name: Customers         Total:      Group By         Crosstab:   Row Heading      NOTE: In versions 1.x and 2.0, there is a space in Company Name.      Field: Expr1: Year([OrderDate]) & " " & "Order Count"         Table Name:         Total: Group By         Crosstab: Column Heading      NOTE: In versions 1.x and 2.0, there is a space in Order Date.      Field: OrderID         Table Name: Orders         Total:      Count         Crosstab:   Value      NOTE: In versions 1.x and 2.0, there is a space in Order ID.					
  2. Select the query by clicking anywhere on the gray area in the top half of the query grid. Click Query Properties on the View menu, select the Fixed Column Headings check box, and in the Fixed Column Headings box type the entries appropriate for your version of Microsoft Access:
          Version 1.x:      "1989 Order Count";"1990 Order Count";"1991 Order                        Count";"1992 Order Count"      Version 2.0:      "1991 Order Count";"1992 Order Count";"1993 Order                        Count";"1994 Order Count"      Version 7.0:      "1993 Order Count";"1994 Order Count";"1995 Order                        Count"      Version 97:       "1994 Order Count";"1995 Order Count";"1996 Order                        Count"					
NOTE: In Microsoft Access versions 2.0, 7.0, 97, theFixedColumnHeadings property is called Column Headings.

Step Three: Create the Multiple Values Query

  1. Create a Select query based on the Order Total and Order Count Crosstab Queries. You will use CompanyName as well as the Order Total and Order Count fields for the years whose results you want to view. The example below uses the last two years of Order Total and Order Count in Microsoft Access 97.
          Query: Multiple Values      ----------------------      Join: Order Total.[CompanyName] <-> Order Count.[CompanyName]      NOTE: In versions 1.x and 2.0, there is a space in Company Name.      Field: CompanyName         Table Name: Order Count         Show: Yes      Field: 1995 Order Count         Table Name: Order Count         Show: Yes      Field: 1995 Order Total         Table Name: Order Total         Show: Yes      Field: 1996 Order Count         Table Name: Order Count         Show: Yes      Field: 1996 Order Total         Table Name: Order Total         Show: Yes					
Running the Multiple Values query results in a table that looks like thefollowing:
 Company Name          1995 Order Count 1995 Order Total 1996 Order Count.. -------------------------------------------------------------------------- Alfred's Futterkiste  3                $2,022.50        3 Ana Trujillo          2                  $799.75        1 Antonio Moreno        5                $5,960.78        1				

NOTE: This technique does not work if the field specified for theconcatenation (Step One(expr1)) is a field in more than one of the tablesjoined in the query. There is no way to force the query to accept thetable name in the expression without generating reserved error -1310. Ifyou add the table name to the Table row, you generate a syntax error. Ifyou leave the table name out completely, you generate an "ambiguousreference" error.
REFERENCES
For more information about crosstab queries, search for "crosstab queries,creating," and then "Create a crosstab query without using a wizard," usingthe Microsoft Access 97 Help Index.
Properties

Article ID: 109939 - Last Review: 01/18/2007 23:42:56 - Revision: 2.1

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition
  • kbinfo KB109939
Feedback