ACC: Displaying Totals for Each Row in Crosstab Queries

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

REFERENCES