ACC: Creating a Crosstab Query with Multiple Value Fields

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

Novice: Requires knowledge of the user interface on single-user computers.

In a Microsoft Access crosstab query, you can specify only one field or calculation as the value. Often it is desirable to show more than one value in the query.

For example, the following shows two columns of information per company, a count of the number of orders, and the order totals for each year:

 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

MORE INFORMATION

In order to create a Multiple Value Fields crosstab query, you must create two separate crosstab queries with fixed column headings for each value 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 Microsoft Access version 7.0 and Microsoft Access 97 (or NWIND.MDB in versions 2.0 or earlier) to create a query that displays results similar to the example above, showing the number of sales and total for each year for each company.

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"
   
   

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"
   
   

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
   
   

 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

REFERENCES