ACC2000: How to Create a Crosstab Query with Multiple Value Fields (209143)

   Company Name  1998 Orders  1998 Total  1999 Orders  1999 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 Field crosstab query, you must create a separate crosstab query for each value that you want to calculate. These queries can then be joined, using a select query to display the desired results.

The following example uses the sample database Northwind.mdb to create a query that displays results similar to the example in the "Summary" section earlier in this article. It shows the number of sales and total for each year for each company.

1. Open the sample database Northwind.mdb 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]
      Join: Orders.[OrderID] <-> Order Details.[OrderID]
   
      Field: CompanyName
         Table Name: Customers
         Total: Group By
         Crosstab: Row Heading
   
      Field: Expr1: Year([OrderDate]) & " " & "Order Total"
         Table Name:
         Total: Group By
         Crosstab: Column Heading
   
     Field: Expr2: Sum(CCur([UnitPrice]*[Quantity]*(1-[Discount])))
         Table Name:
         Total: Expression
         Crosstab: Value
   					

2. Create the following crosstab query based on the Orders and the Customers tables:

      Query: Order Count
      ------------------
      Type: Crosstab Query
      Join: Customers.[CustomerID] <-> Orders.[CustomerID]
   
      Field: CompanyName
         Table Name: Customers
         Total:      Group By
         Crosstab:   Row Heading
   
      Field: Expr1: Year([OrderDate]) & " " & "Order Count"
         Table Name:
         Total: Group By
         Crosstab: Column Heading
   
      Field: OrderID
         Table Name: Orders
         Total:      Count
         Crosstab:   Value
   					

3. Create a 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 following example uses the last two years of Order Total and Order Count in Microsoft Access.

      Query: Multiple Values
      ----------------------
      Join: Order Total.[CompanyName] <-> Order Count.[CompanyName]
   
      Field: CompanyName
         Table Name: Order Count
   
      Field: 1997 Order Count
         Table Name: Order Count
   
      Field: 1997 Order Total
         Table Name: Order Total
   
      Field: 1998 Order Count
         Table Name: Order Count
   
      Field: 1998 Order Total
         Table Name: Order Total
   					

Running the Multiple Values query results in a table that looks like the following:

                         1997 Order  1997 Order  1998 Order  1998 Order
   Company Name            Count       Total       Count       Total
   --------------------------------------------------------------------
   Alfred's Futterkiste        3      $2,022.50        3      $2,022.50
   Ana Trujillo                2        $799.75        1        $514.10
   Antonio Moreno              5      $5,960.78        1        $660.00
				

NOTE: You need to add the table name to the expression if the field specified for the concatenation is a field in more than one of the tables joined in the query. For example, in Step 1 you would change:

   Year([OrderDate]) & " " & "Order Total"
				

to:

   Year([Orders].[OrderDate]) & " " & "Order Total"
				

If you add the table name to the Table row, you generate a syntax error. If you leave the table name out completely, you generate an ambiguous reference error.