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