Microsoft KB Archive/102517

= ACC: Displaying Totals for Each Row in Crosstab Queries =

Article ID: 102517

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q102517



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
 Open the sample database NWIND.MDB.  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  Save the query as XTAB With Row Totals, and then run the query.

In Microsoft Access 7.0 and 97
 Open the sample database Northwind.mdb. Create a new query in Design view and add the following tables to the query: Customers, Orders, and Orders Subtotals.</li>  Add the following fields to the query:

<pre class="fixed_text">     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 </li> Save the query as XTAB With Row Totals, and then run the query.</li></ol>

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.

<div class="references_section">