ACC: Displaying Totals for Each Row in Crosstab Queries

ID: Q102517

The information in this article applies to:

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