You are currently offline, waiting for your internet to reconnect

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

This article was previously published under Q209143
This article has been archived. It is offered "as is" and will no longer be updated.
For a Microsoft Access 2002 version of this article, see 304458.
SUMMARY
Novice: Requires knowledge of the user interface on single-user computers.

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

NOTE: You can see a demonstration of the technique that is used in this article in the sample file Qrysmp00.exe. For information about how to obtain this sample file, please see the following article in the MicrosoftKnowledge Base:
207626 ACC2000: Access 2000 Sample Queries Available in Download Center
MORE INFORMATION
In order to create a Multiple Value Field crosstab query, you mustcreate 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 thefollowing:
                         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.
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.
inf Qrysmp00 exe
Properties

Article ID: 209143 - Last Review: 12/05/2015 11:03:18 - Revision: 1.0

Microsoft Access 2000 Standard Edition

  • kbnosurvey kbarchive kbhowto KB209143
Feedback