cross tab column sum

[NetworkTrade]

Access07; haven't used CrossTab queries before;

initial set up is fine; weeks along top, employees are rows, values in
grid ok...

the wizard allowed me to easily sum each row; which I need

how does one sum the columns? there are 52 weeks and so am hoping to
avoid a manual coding approach for each...

figured it might be a common question but have searched around on this site
and haven't come up with that q/a...

tia

 

[John Spencer]

You can't in a query unless you use a union query. Are you using the
crosstab query as the source for a report? If so, could you use a subreport
based on another crosstab that didn't group by employees?

You might post the SQL of the crosstab. (View: SQL from the menu).

The Union SQL might look something like the following.

Transform Sum(SomeValue) as X
SELECT "Detail" as LineType
, EmployeeName
, Sum(SomeValue) as Total
FROM SomeTable
GROUP BY "Detail", EmployeeName
PIVOT DatePart("ww",DateField) in (1,2,3,4,5,6,7,8,9,...51,52,53)

UNION ALL

Transform Sum(SomeValue) as X
SELECT "Total" as LineType,
"" as EmployeeName
, Sum(SomeValue) as Total
FROM SomeTable
GROUP BY "Total", ""
PIVOT DatePart("ww",DateField) in (1,2,3,4,5,6,7,8,9,...51,52,53)



--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[NetworkTrade]

ah ok - well then I thought I had missed something either in the wizard or in
some of the newer features of Access07 that I haven't yet fully
explored....figured it was a common need and a shortcut already existed...

have a bit of a fear that report won't like 52 columns....will noodle about
w/ your sample code and see if I can make it work...thanks for fast reply....

 

[NetworkTrade]

well I have both have of the code working - but not together...

above the UNION ALL of course just came from sql view of the existing
crosstab query...

wrote the code but got a syntax error with the cursor at the UNION
word...saying that there is a missing operator

put the lower half of code below the UNION ALL statement into its own query
and it works fine...

 

[NetworkTrade]

TRANSFORM Sum(Weekly1.EstWT) AS SumOfEstWT
SELECT Weekly1.Employee, Sum(Weekly1.EstWT) AS Total
FROM Weekly1
GROUP BY Weekly1.Employee
PIVOT Weekly1.WeekInfo
UNION ALL
TRANSFORM Sum(Weekly1.EstWT) AS X
SELECT "Total" AS LineType, "" AS Employee, Sum(Weekly1.EstWT) AS Total
FROM Weekly1
GROUP BY "Total", ""
PIVOT Weekly1.WeekInfo;

 

[Duane Hookom]

It doesn't look like your two selects in your union query return the same
number of columns. Union queries must have the same column count. Try:

TRANSFORM Sum(Weekly1.EstWT) AS SumOfEstWT
SELECT Weekly1.Employee, Sum(Weekly1.EstWT) AS Total
FROM Weekly1
GROUP BY Weekly1.Employee
PIVOT Weekly1.WeekInfo
UNION ALL
TRANSFORM Sum(Weekly1.EstWT) AS X
SELECT "Total", Sum(Weekly1.EstWT) AS Total
FROM Weekly1
GROUP BY "Total"
PIVOT Weekly1.WeekInfo;

 

[NetworkTrade]

same problem

code above UNION ALL works
and code below UNION ALL works

but not working together.... missing operator...

 

[Duane Hookom]

Sorry. I forgot you can't union two TRANSFORM statements. You need to save
each crosstab separately and then use something like:
SELECT *
FROM qxtb1
UNION ALL
SELECT *
FROM qxtb2;

 

[NetworkTrade]

BINGO !!

much thanks Duane....
--
NTC

Sorry. I forgot you can't union two TRANSFORM statements. You need to save
each crosstab separately and then use something like:
SELECT *
FROM qxtb1
UNION ALL
SELECT *
FROM qxtb2;

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

 

[Mark Wiley]

....you just need to add your value column a second time and set the column to a row heading. Works great.



NetworkTrad wrote:

cross tab column sum
07-Jan-08

Access07; haven't used CrossTab queries before

initial set up is fine; weeks along top, employees are rows, values in
grid ok..

the wizard allowed me to easily sum each row; which I nee

how does one sum the columns? there are 52 weeks and so am hoping to
avoid a manual coding approach for each..

figured it might be a common question but have searched around on this site
and haven't come up with that q/a..

ti

--
NTC

Previous Posts In This Thread:

cross tab column sum
Access07; haven't used CrossTab queries before

initial set up is fine; weeks along top, employees are rows, values in
grid ok..

the wizard allowed me to easily sum each row; which I nee

how does one sum the columns? there are 52 weeks and so am hoping to
avoid a manual coding approach for each..

figured it might be a common question but have searched around on this site
and haven't come up with that q/a..

ti

--
NTC

You can't in a query unless you use a union query.
You can't in a query unless you use a union query. Are you using the
crosstab query as the source for a report? If so, could you use a subreport
based on another crosstab that didn't group by employees

You might post the SQL of the crosstab. (View: SQL from the menu)

The Union SQL might look something like the following

Transform Sum(SomeValue) as
SELECT "Detail" as LineTyp
, EmployeeNam
, Sum(SomeValue) as Tota
FROM SomeTabl
GROUP BY "Detail", EmployeeNam
PIVOT DatePart("ww",DateField) in (1,2,3,4,5,6,7,8,9,...51,52,53

UNION AL

Transform Sum(SomeValue) as
SELECT "Total" as LineType
"" as EmployeeNam
, Sum(SomeValue) as Tota
FROM SomeTabl
GROUP BY "Total", "
PIVOT DatePart("ww",DateField) in (1,2,3,4,5,6,7,8,9,...51,52,53

 

[KARL DEWEY]

Totals in last row of crosstab query is possible using a union ahead of the
crosstab.
The union to have a select query with added 'Sort' field with "0". Second
part of union to be totals query with added 'Sort' field with "1". If your
crosstab to have dates then format them in the union query for final display.

In the crosstab set Sort field Group By and Ascending but with out Row,
Column, or Value designator. Set the other row fields Ascending to the right
of the Sort field.