ACC: Displaying Totals for Each Row in Crosstab Queries

ID: Q102517


The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97


SUMMARY

Crosstab queries can display totals for each row. To do this, simply repeat the field used as the crosstab value and make it a crosstab row header.


MORE INFORMATION

The following steps explain how to create a crosstab query with row totals:

In Microsoft Access 1.x and 2.0


  1. Open the sample database NWIND.MDB.


  2. Create a new query based on the Order Review query, as follows:
    
          Query: XTAB With Row Totals
          ---------------------------------
          Field: Company Name
             Total: Group By
             CrossTab: Row Heading
    
          Field: Total Ordered: Subtotal (Subtotal is Order Amount in Microsoft
                                          Access 1.x)
             Total: Sum
             CrossTab: Row Heading
    
          Field: Ship Via
             Total: Group By
             CrossTab: Column Heading
    
          Field: Subtotal
             Total: Sum
             CrossTab: Value 


  3. Save the query as XTAB With Row Totals, and then run the query.


In Microsoft Access 7.0 and 97


  1. Open the sample database Northwind.mdb.


  2. Create a new query in Design view and add the following tables to the query: Customers, Orders, and Orders Subtotals.


  3. Add the following fields to the query:
    
          Query: XTAB With Row Totals
          ---------------------------------
          Field: CompanyName
          Table: Customers
             Total: Group By
             CrossTab: Row Heading
    
          Field: Total Ordered: Subtotal
          Table: Order Subtotals
             Total: Sum
             CrossTab: Row Heading
    
          Field: ShipVia
          Table: Orders
             Total: Group By
             CrossTab: Column Heading
    
          Field: Subtotal
          Table: Order Subtotals
             Total: Sum
             CrossTab: Value 


  4. Save the query as XTAB With Row Totals, and then run the query.


NOTE: The Total column always appears before the other data columns, but you can display the data on a form or report in any order.


REFERENCES

For more information on creating crosstab queries, search the Help Index for crosstab queries, creating," or ask the Microsoft Access 97 Office Assistant.

Keywords : kbusage QryCross
Version : 1.0 1.1
Platform : WINDOWS
Issue type : kbinfo


Last Reviewed: March 25, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.