Cross Tab Quert Totals

[Ananth]

In the Access Cross Tab Query, using the wizard, is it possible to see total
of Columns & Rows along with Query Results. Every time, I run the cross-tab
query, I am copying the results to the EXCEL for get the Row & Column Totals

Results of Cross Tab Query

Spend-1 Spend-2 Spend-3
Row1 100 200 300
Row2 400 500 600
Row3 700 800 900

What I want to see is

Spend-1 Spend-2 Spend-3 Total
Row1 100 200 300 600
Row2 400 500 600 1500
Row3 700 800 900 2400
Total 1200 1500 1800 4500

Any help please

 

[Arvin Meyer [MVP]]

No, you can't do that in an Access query. You can easily do it in a report
based on that query. Simply put a footer on the report with textboxes like:

=Sum([Spend-1]) =Sum([Spend-2]) =Sum([Spend-3]) =Sum([Total])

where Total is the name of the textbox with the row total.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

 

[Pieter Wijnen]

not exactly true
you can include Sum(FieldName) As Total in the SELECT Statement

TRANSFORM Sum(A.SPEND)
SELECT A.DEPARTMENT, Sum(A.SPEND) FROM TheTable A
GROUP BY A.DEPARTMENT
PIVOT A.SPENDTYPE

Pieter

No, you can't do that in an Access query. You can easily do it in a report
based on that query. Simply put a footer on the report with textboxes
like:

=Sum([Spend-1]) =Sum([Spend-2]) =Sum([Spend-3]) =Sum([Total])

where Total is the name of the textbox with the row total.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In the Access Cross Tab Query, using the wizard, is it possible to see
total
of Columns & Rows along with Query Results. Every time, I run the
cross-tab
query, I am copying the results to the EXCEL for get the Row & Column
Totals

Results of Cross Tab Query

Spend-1 Spend-2 Spend-3
Row1 100 200 300
Row2 400 500 600
Row3 700 800 900

What I want to see is

Spend-1 Spend-2 Spend-3 Total
Row1 100 200 300 600
Row2 400 500 600 1500
Row3 700 800 900 2400
Total 1200 1500 1800 4500

Any help please

 

[Arvin Meyer [MVP]]

You show a total of only 1 column, which doesn't answer his question.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

"Pieter Wijnen"

not exactly true
you can include Sum(FieldName) As Total in the SELECT Statement

TRANSFORM Sum(A.SPEND)
SELECT A.DEPARTMENT, Sum(A.SPEND) FROM TheTable A
GROUP BY A.DEPARTMENT
PIVOT A.SPENDTYPE

Pieter

No, you can't do that in an Access query. You can easily do it in a
report based on that query. Simply put a footer on the report with
textboxes like:

=Sum([Spend-1]) =Sum([Spend-2]) =Sum([Spend-3]) =Sum([Total])

where Total is the name of the textbox with the row total.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In the Access Cross Tab Query, using the wizard, is it possible to see
total
of Columns & Rows along with Query Results. Every time, I run the
cross-tab
query, I am copying the results to the EXCEL for get the Row & Column
Totals

Results of Cross Tab Query

Spend-1 Spend-2 Spend-3
Row1 100 200 300
Row2 400 500 600
Row3 700 800 900

What I want to see is

Spend-1 Spend-2 Spend-3 Total
Row1 100 200 300 600
Row2 400 500 600 1500
Row3 700 800 900 2400
Total 1200 1500 1800 4500

Any help please

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!

 

[Pieter Wijnen]

That is correct, you must use a running sum on a report (form) for that, and
that's where you'd need it anyway. My oversight

Pieter

You show a total of only 1 column, which doesn't answer his question.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

"Pieter Wijnen"

not exactly true
you can include Sum(FieldName) As Total in the SELECT Statement

TRANSFORM Sum(A.SPEND)
SELECT A.DEPARTMENT, Sum(A.SPEND) FROM TheTable A
GROUP BY A.DEPARTMENT
PIVOT A.SPENDTYPE

Pieter

No, you can't do that in an Access query. You can easily do it in a
report based on that query. Simply put a footer on the report with
textboxes like:

=Sum([Spend-1]) =Sum([Spend-2]) =Sum([Spend-3]) =Sum([Total])

where Total is the name of the textbox with the row total.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

In the Access Cross Tab Query, using the wizard, is it possible to see
total
of Columns & Rows along with Query Results. Every time, I run the
cross-tab
query, I am copying the results to the EXCEL for get the Row & Column
Totals

Results of Cross Tab Query

Spend-1 Spend-2 Spend-3
Row1 100 200 300
Row2 400 500 600
Row3 700 800 900

What I want to see is

Spend-1 Spend-2 Spend-3 Total
Row1 100 200 300 600
Row2 400 500 600 1500
Row3 700 800 900 2400
Total 1200 1500 1800 4500

Any help please

--------------------------------------------------------------------------------
I am using the free version of SPAMfighter for private users.
It has removed 4388 spam emails to date.
Paying users do not have this message in their emails.
Try SPAMfighter for free now!