Crosstab Column Total

[raskew]

Hi -

I've got myself 'wrapped around the axle' trying to create a query (which will then be used in a Union Query).

Have a dynamic crosstab query which produces by-month totals
for each category. The crosstab is designed to total the input
month and the previous 5 months (6 months total).

[B]Category	Dec 08	Jan 09	Feb 09	Mar 09	Apr 09	May 09[/B]
[I]Alpha[/I]	            $0.00	$0.00	$0.00	$0.00	$6.75	$0.00
[I]Bravo[/I]	            $0.00	$16.20	$0.00	$37.40	$0.00	$0.00
[I]Charlie[/I]	            $151.45	$73.75	$78.00	$195.36	$136.12	$62.70

Need to produce a one-row totals query that will return monthly
totals, e.g.

[B]Category	Dec 08	Jan 09	Feb 09	Mar 09	Apr 09	May 09[/B]
[I]Totals[/I]	            $151.45	$89.95	$78.00	$232.76	$142.87	$62.70

Have hassled with this for two days, on and off. Any wisdom greatly apprreciated.

Thanks - Bob

 

[Banana]

I'm going to make a guess as I don't know the SQL you used for your crosstab query, but if you referenced a query that summed months grouped by category then by month (or maybe month then category), then you would need another query that sum the balance with a group by month only (omit the category), then you can make a crosstab query based on that query, UNION it with the first crosstab query.

Did that make sense?

 

[pbaldy]

Maybe I'm missing something. I assume you're grouping on the category; what happens if you remove that from the SELECT and GROUP BY clauses?

 

[HiTechCoach]

Do you need the total line in the actual query or just on the report?

This may help:
Dynamic report from crosstab

 

[raskew]

Paul, Banana -

Thanks for the quick responses.

Here's the original code, which works as advertised:

TRANSFORM Format(nz(Sum([tblTransfer].[PayAmt]),0),"Currency") AS SumOfPayAmt
SELECT tblCat.Category, Sum(tblTransfer.PayAmt) AS Total, Format(Sum([tblTransfer].[PayAmt])/6,"$#.00") AS Avg
FROM tblTransfer LEFT JOIN tblCat ON tblTransfer.CatID = tblCat.CatID
WHERE (((CDate(Format([expdte],"mm/yyyy"))) Between #12/1/2008# And #5/31/2009#))
GROUP BY tblCat.Category
ORDER BY tblCat.Category, Format([expdte],"mmm yy")
PIVOT Format([expdte],"mmm yy") In ('Dec 08','Jan 09','Feb 09','Mar 09','Apr 09','May 09');

When I remove reference to tblCat.Category, I end up with this:

TRANSFORM Format(nz(Sum([tblTransfer].[PayAmt]),0),"Currency") AS SumOfPayAmt
SELECT Sum(tblTransfer.PayAmt) AS Total, Format(Sum([tblTransfer].[PayAmt])/6,"$#.00") AS Avg
FROM tblTransfer 
WHERE (((CDate(Format([expdte],"mm/yyyy"))) Between #12/1/2008# And #5/31/2009#))
ORDER BY Format([expdte],"mmm yy")
PIVOT Format([expdte],"mmm yy") In ('Dec 08','Jan 09','Feb 09','Mar 09','Apr 09','May 09');

...and, when I attempt to run it I get a:

Syntax error in TRANSFORM statement.

This query returns the desired result (in terms of totals), but instead of returning one row, it returns a row for each month.

TRANSFORM Format(nz(Sum([tblTransfer].[PayAmt]),0),"Currency") AS SumOfPayAmt
SELECT Sum(tblTransfer.PayAmt) AS Total, Format(Sum([tblTransfer].[PayAmt])/6,"$#.00") AS Avg
FROM tblTransfer
WHERE (((CDate(Format([expdte],"mm/yyyy"))) Between #12/1/2008# And #5/31/2009#))
GROUP BY Format([expdte],"mmm yy")
ORDER BY Format([expdte],"mmm yy")
PIVOT Format([expdte],"mmm yy") In ('Dec 08','Jan 09','Feb 09','Mar 09','Apr 09','May 09');

*******************

Banana, I'm looking at creating a query per your suggestion.

Thanks to you both - Bob

 

[raskew]

Boyd -

I'm hoping to include the totals line with the query.

This query returns a one-month view and a totals row.

SELECT tblCat.Category, Sum(tblTransfer.PayAmt) AS SumOfPayAmt, 1 as SortMe
FROM tblCat RIGHT JOIN tblTransfer ON tblCat.CatID = tblTransfer.CatID
WHERE (((tblTransfer.ExpDte) Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1))
GROUP BY tblCat.Category
UNION SELECT "Grand Total", Sum(tblTransfer.PayAmt) AS MonthlyTotal, 2 as SortMe
FROM tblTransfer
WHERE (((tblTransfer.ExpDte) Between DateValue([enter mm/yyyy]) And DateAdd("m",1,DateValue([enter mm/yyyy]))-1))
ORDER BY SortMe, Category;

I'm trying to duplicate this, but showing more than one month.

Bob