ACC2000: Creating a Crosstab Query with Multiple Value Fields

• Microsoft Access 2000

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, you may want to show more than one value in the query.

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

   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 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 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 

                         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 

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

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

REFERENCES

For more information about crosstab queries, click Microsoft Access Help on the Help menu, type crosstab queries in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.

Additional query words: inf

Keywords : kbdta QryCross
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto