ACC: Displaying Totals for Each Row in Crosstab Queries

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.