Row Total for each month? Crosstab Query

D

david.isaacks

TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT qry_HB_ALL.Area, Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY qry_HB_ALL.Area
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


I current have a count for each area, but I would also like a total
count for each month as a row heading as the very last row.

Thanks,
David

Total Jan Feb March May
Area1
Area2
Area3
Total * * * * *
*Would like a row heading for total each month
 
G

Guest

I used several queries and table named HB_ALL.

HB_ALL_Count --
SELECT HB_ALL.Area, Format([Opened],"mmm") AS Expr1, Count(HB_ALL.[SR #])
AS [CountOfSR #]
FROM HB_ALL
GROUP BY HB_ALL.Area, Format([Opened],"mmm");

HB_ALL_Total –
SELECT "Total" AS Area, Format([Opened],"mmm") AS Expr1, Count(HB_ALL.[SR
#]) AS [CountOfSR #]
FROM HB_ALL
GROUP BY "Total", Format([Opened],"mmm");

HB_ALL_wTotal --
SELECT HB_ALL_Count.Area, Expr1, HB_ALL_Count.[CountOfSR #]
FROM HB_ALL_Count
UNION SELECT HB_ALL_Total.Area, HB_ALL_Total.Expr1, HB_ALL_Total.[CountOfSR #]
FROM HB_ALL_Total;

HB_ALL_wTotal_Crosstab –
TRANSFORM First(HB_ALL_wTotal.[CountOfSR #]) AS [FirstOfCountOfSR #]
SELECT HB_ALL_wTotal.Area, First(HB_ALL_wTotal.[CountOfSR #]) AS [Total Of
CountOfSR #]
FROM HB_ALL_wTotal
GROUP BY HB_ALL_wTotal.Area
PIVOT HB_ALL_wTotal.Expr1 In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
 
M

MGFoster

TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT qry_HB_ALL.Area, Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY qry_HB_ALL.Area
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


I current have a count for each area, but I would also like a total
count for each month as a row heading as the very last row.

Thanks,
David

Total Jan Feb March May
Area1
Area2
Area3
Total * * * * *
*Would like a row heading for total each month
Click to expand...

You can't have columns' totals in a query, you have to use a report or a
form.
 
G

Guest

Doing some more checking I found an error in the crosstab.
Here it is fixed --
TRANSFORM First(HB_ALL_wTotal.[CountOfSR #]) AS [FirstOfCountOfSR #]
SELECT HB_ALL_wTotal.Area, Sum(HB_ALL_wTotal.[CountOfSR #]) AS [Total Of
CountOfSR #]
FROM HB_ALL_wTotal
GROUP BY HB_ALL_wTotal.Area
PIVOT HB_ALL_wTotal.Expr1 In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


KARL DEWEY said:
I used several queries and table named HB_ALL.

HB_ALL_Count --
SELECT HB_ALL.Area, Format([Opened],"mmm") AS Expr1, Count(HB_ALL.[SR #])
AS [CountOfSR #]
FROM HB_ALL
GROUP BY HB_ALL.Area, Format([Opened],"mmm");

HB_ALL_Total –
SELECT "Total" AS Area, Format([Opened],"mmm") AS Expr1, Count(HB_ALL.[SR
#]) AS [CountOfSR #]
FROM HB_ALL
GROUP BY "Total", Format([Opened],"mmm");

HB_ALL_wTotal --
SELECT HB_ALL_Count.Area, Expr1, HB_ALL_Count.[CountOfSR #]
FROM HB_ALL_Count
UNION SELECT HB_ALL_Total.Area, HB_ALL_Total.Expr1, HB_ALL_Total.[CountOfSR #]
FROM HB_ALL_Total;

HB_ALL_wTotal_Crosstab –
TRANSFORM First(HB_ALL_wTotal.[CountOfSR #]) AS [FirstOfCountOfSR #]
SELECT HB_ALL_wTotal.Area, First(HB_ALL_wTotal.[CountOfSR #]) AS [Total Of
CountOfSR #]
FROM HB_ALL_wTotal
GROUP BY HB_ALL_wTotal.Area
PIVOT HB_ALL_wTotal.Expr1 In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



[email protected] said:
TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT qry_HB_ALL.Area, Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY qry_HB_ALL.Area
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


I current have a count for each area, but I would also like a total
count for each month as a row heading as the very last row.

Thanks,
David

Total Jan Feb March May
Area1
Area2
Area3
Total * * * * *
*Would like a row heading for total each month
Click to expand...
Click to expand...
 
D

Duane Hookom

You could create another crosstab like:
TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT "Total" as [Area], Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY "Total"
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Then use a UNION ALL query to join the two crosstabs:

SELECT *
FROM qxtbOne
UNION ALL
SELECT *
FROM qxtbTwo;
 
D

david.isaacks

Thank you, it work great.

David

KARL said:
Doing some more checking I found an error in the crosstab.
Here it is fixed --
TRANSFORM First(HB_ALL_wTotal.[CountOfSR #]) AS [FirstOfCountOfSR #]
SELECT HB_ALL_wTotal.Area, Sum(HB_ALL_wTotal.[CountOfSR #]) AS [Total Of
CountOfSR #]
FROM HB_ALL_wTotal
GROUP BY HB_ALL_wTotal.Area
PIVOT HB_ALL_wTotal.Expr1 In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


KARL DEWEY said:
I used several queries and table named HB_ALL.

HB_ALL_Count --
SELECT HB_ALL.Area, Format([Opened],"mmm") AS Expr1, Count(HB_ALL.[SR #])
AS [CountOfSR #]
FROM HB_ALL
GROUP BY HB_ALL.Area, Format([Opened],"mmm");

HB_ALL_Total -
SELECT "Total" AS Area, Format([Opened],"mmm") AS Expr1, Count(HB_ALL.[SR
#]) AS [CountOfSR #]
FROM HB_ALL
GROUP BY "Total", Format([Opened],"mmm");

HB_ALL_wTotal --
SELECT HB_ALL_Count.Area, Expr1, HB_ALL_Count.[CountOfSR #]
FROM HB_ALL_Count
UNION SELECT HB_ALL_Total.Area, HB_ALL_Total.Expr1, HB_ALL_Total.[CountOfSR #]
FROM HB_ALL_Total;

HB_ALL_wTotal_Crosstab -
TRANSFORM First(HB_ALL_wTotal.[CountOfSR #]) AS [FirstOfCountOfSR #]
SELECT HB_ALL_wTotal.Area, First(HB_ALL_wTotal.[CountOfSR #]) AS [Total Of
CountOfSR #]
FROM HB_ALL_wTotal
GROUP BY HB_ALL_wTotal.Area
PIVOT HB_ALL_wTotal.Expr1 In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



[email protected] said:
TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT qry_HB_ALL.Area, Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY qry_HB_ALL.Area
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


I current have a count for each area, but I would also like a total
count for each month as a row heading as the very last row.

Thanks,
David

Total Jan Feb March May
Area1
Area2
Area3
Total * * * * *
*Would like a row heading for total each month
Click to expand...
Click to expand...
Click to expand...
 
D

david.isaacks

Duane,

This worked great also, plus it ran a alittle faster when I used it in
the report.

David

Duane said:
You could create another crosstab like:
TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT "Total" as [Area], Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY "Total"
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Then use a UNION ALL query to join the two crosstabs:

SELECT *
FROM qxtbOne
UNION ALL
SELECT *
FROM qxtbTwo;


--
Duane Hookom
MS Access MVP


TRANSFORM Count(qry_HB_ALL.[SR #]) AS [CountOfSR #]
SELECT qry_HB_ALL.Area, Count(qry_HB_ALL.[SR #]) AS [Total Of SR #]
FROM qry_HB_ALL
GROUP BY qry_HB_ALL.Area
PIVOT Format([Opened],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


I current have a count for each area, but I would also like a total
count for each month as a row heading as the very last row.

Thanks,
David

Total Jan Feb March May
Area1
Area2
Area3
Total * * * * *
*Would like a row heading for total each month
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group by Range in Crosstab Query 4
CrossTab Query Format 2
Crosstab - Multiple values on each row 5
UNION and Crosstab query 5
Crosstab qry not adding up 1
Crosstab By Month with only last 6 months data 6
Crosstab, Quarter Sum isn't working 6
crosstab query total column 1

Top