crosstab query

[Guest]

Can I total both the columns and the rows in a crosstab query? If not
crosstab, then how please?

I would like my results to looks like this:

C1 C2 C3 (Total)
Row1 1 6 8 15
Row2 3 7 2 12
(Total) 4 13 10 27

 

[Guest]

I managed it by totalling the rows in a crosstab and then also summing the
columns in a report if that's any help to you?

 

[Michael Gramelspacher]

Subject: crosstab query
From: pdit <[email protected]>
Newsgroups: microsoft.public.access.queries

Can I total both the columns and the rows in a crosstab query? If not
crosstab, then how please?

I would like my results to looks like this:

C1 C2 C3 (Total)
Row1 1 6 8 15
Row2 3 7 2 12
(Total) 4 13 10 27

One way:

SELECT table1.c1,
table1.c2,
table1.c3,
SUM(Nz([c1]) + Nz([c2]) + Nz([c3])) AS total
FROM table1
GROUP BY table1.c1,
table1.c2,
table1.c3
UNION
SELECT SUM(table1.c1) AS sumofc1,
SUM(table1.c2) AS sumofc2,
SUM(table1.c3) AS sumofc3,
SUM(Nz([c1]) + Nz([c2]) + Nz([c3])) AS total

 

[Guest]

Sorry, I should have also said: -
Create a report from the crosstab query and then add a textbox to the report
and type =Sum([NameOfYourColumn]) in the 'Control Source'