Query to total columns AND rows

[Christine Pearc]

Is it possible to total columns in a query? Right now, I have a query that produces the following column counts, but I'd like to total Pending, Overdue, etc. This data is being displayed in a subform.

Process Pending Overdue Total
-------------------------------------
Engineering 1 2 3
Procurement 0 6 6
<etc>
-------------------------------------
TOTAL 1 8 9 <- this is the line I want to add

Here's what the query (qryStatusRptB) looks like thus far:
Field: Process
Table: tblProcesses
Total: Group By

Field: Pending
Table: qryStatusRptA
Total: Sum

Field: Overdue
Table: queryStatusRptA
Total: Sum

Field: TotalRecords: Count(*)
Table:
Total: Expression

Can someone explain how to get the TOTAL ROW in here? (I can do it via another query, but that won't work since the data is displayed in a subform. I've tried crosstabs without success.)

Many thanks,
Christine

 

[TessB]

I haven't tried this myself, but I wonder if a Union query would solve your problem.

You would modify your current query by adding this to the SQL:

UNION
Select "Total" AS [Process], Sum([qryStatusRptA].[Pending]), Sum([qryStatusRptA].[Overdue]), Count(*)

Make a copy of your original query and save it as a test query to test out the theory. (Wouldn't want you to lose valueable work on my account) Let me know if it works!

Tess

 

[Pat Hartman]

The fact that you are asking how to total the columns of a row leads me to the conclusion that you created a spreadsheet and called it a table. Relational databases, not just Access, do not provide any aggregate function that will work on the columns of a single row. You have two choices.
1. normalize the table. Then a crosstab will solve your problem.
or
2. Do the calculation yourself:
Select fldA, (Nz(Process,0) + Nz(Pending,0) + Nz(OverDue,0)) as YourSum, fldB, etc.