Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. How to create a crosstab query with multiple value fields

How to create a crosstab query with multiple value fields

Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.accdb and .mdb).
For a Microsoft Access 2000 version of this article, see
209143 .
For a Microsoft Access 97 version of this article, see
109939 .

↑ Back to the top

Summary

In a Microsoft Access crosstab query, you can specify only one field or calculation as the value. You may want to show more than one value in the query.

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
This type of query is sometimes called a Multiple Value Field query.

↑ Back to the top

More Information

To create a Multiple Value Field crosstab query, you must create a separate crosstab query for each value that you want to calculate. You can then use a select query to join these crosstab queries to display the results that you want.

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 then create the following crosstab query that is 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. Save this query as Order Total.
  3. Create the following crosstab query that is 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
  4. Save this query as Order Count.
  5. Create a query that is based on the Order Total and the Order Count crosstab queries. Use the CompanyName, 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
  6. When you run the Multiple Values query, you will see a table that looks similar to the following table:

                             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 must add the table name to the expression if the field that is specified for the concatenation is a field in more than one of the tables that is 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.

↑ Back to the top

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.

↑ Back to the top

Keywords: kboffice12yes, kbfreshness2006, kbquery, kbsweptsoltax, kboffice2003yes, kbswept, kbhowto, kb

↑ Back to the top

Article Info
Article ID : 304458
Revision : 3
Created on : 4/17/2018
Published on : 4/17/2018
Exists online : False
Views : 1245