Crosstab column totals

  • Thread starter Thread starter Jack Sheet
  • Start date Start date
J

Jack Sheet

Hi all

I have a crosstab query

TRANSFORM Count(Q_Tasks_SATRs_OS.ID_Tasks) AS CountOfID_Tasks
SELECT Q_Tasks_SATRs_OS.Tax, Count(Q_Tasks_SATRs_OS.ID_Tasks) AS [Total Of
ID_Tasks]
FROM Q_Tasks_SATRs_OS
GROUP BY Q_Tasks_SATRs_OS.Tax
PIVOT Q_Tasks_SATRs_OS.Partner;

I want to produce a report of the above, which includes totals under each
column. I get a nice report using the wizard, in all respects but for the
omission of the totals.

I am unsure whether the totals should be generated somewhere in the query,
or using a formula in a text box in a report. I am conscious that in theory
the number of columns can be variable depending on the underlying data, so
my instinct is that it has to be something in the query.

Any help available on this? Sounds like a fairly standard sort of thing to
want to achieve, but I went round the houses in the Help system without
getting anywhere.
 
What kind of variables are your columns? If you can preset them like in the
following queries it would solve your problem.
TRANSFORM First(IIf([Name] Is Null,Null,"X")) AS x
SELECT AttendanceTable.Name
FROM AttendanceTable
WHERE (((Format([Present_Date],"yyyymm"))=Format(DateAdd("m",-Val([Enter 0
for this month or 1 for last month]),Date()),"yyyymm")))
GROUP BY AttendanceTable.Name
PIVOT Val(Format([Present_Date],"d")) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

TRANSFORM Count([Troy_s Table2].Temp) AS CountOfTemp
SELECT [Troy_s Table2].Color, [Troy_s Table2].Level
FROM [Troy_s Table2]
GROUP BY [Troy_s Table2].Color, [Troy_s Table2].Level
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And 239.999,"Warm","Hot"));
 
Karl, thank you for taking the trouble to help, and I am so sorry that I
don't understand any of it, as I take it as a kind of duty to try to make it
worth responder's time and effort to respond, at least to understand and
implement it. I don't pretend to understand how my original crosstab query
worked - I just used the Wizard and switched to SQL view to post it here.
The principle was very simple. There are a number of supervisors, listed
under field Qry.Partner, and a larger number of underlings, listed under
field Qry.Tax. Between them they have to undertake tasks, identified by
Qry.ID_Tasks. Each record in Qry represents a single task, and qry.ID_Tasks
contains no duplicates. No underling reports exclusively to any individual
supervisor, but only one underling and only one supervisor controls any
particular task. The crosstab query is supposed to list the supervisors as
column headings, the underlings as row headings, and the total number of
tasks in the middle of the grid, with rows totalled and columns totalled.
So far I get the rows totalled without any problem, as there is a tick box
in the wizard specifically for that purpose. It is just getting the column
totals that is causing the aggravation.

Thus, my example daty in Qry might be:
ID_Tasks.....Partner ......Tax
1..................AAA..........Ted
2..................AAA..........Bill
3.................BBB...........Sam
4.................BBB..........Sam
5..................BBB..........Ben
6..................AAA.........Ben

So far my crosstab query successfully shows

Tax........RowTotal........AAA.......BBB
Ted.................1................1............0
Bill...................1...............1.............0
Sam.................2................0...........2
Ben..................2...............1............1

All I am missing is another row:
ColTotal.........6................3............3
which I am quite happy to appear in a report rather than the source query.

KARL DEWEY said:
What kind of variables are your columns? If you can preset them like in
the
following queries it would solve your problem.
TRANSFORM First(IIf([Name] Is Null,Null,"X")) AS x
SELECT AttendanceTable.Name
FROM AttendanceTable
WHERE (((Format([Present_Date],"yyyymm"))=Format(DateAdd("m",-Val([Enter 0
for this month or 1 for last month]),Date()),"yyyymm")))
GROUP BY AttendanceTable.Name
PIVOT Val(Format([Present_Date],"d")) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

TRANSFORM Count([Troy_s Table2].Temp) AS CountOfTemp
SELECT [Troy_s Table2].Color, [Troy_s Table2].Level
FROM [Troy_s Table2]
GROUP BY [Troy_s Table2].Color, [Troy_s Table2].Level
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And
239.999,"Warm","Hot"));


Jack Sheet said:
Hi all

I have a crosstab query

TRANSFORM Count(Q_Tasks_SATRs_OS.ID_Tasks) AS CountOfID_Tasks
SELECT Q_Tasks_SATRs_OS.Tax, Count(Q_Tasks_SATRs_OS.ID_Tasks) AS [Total
Of
ID_Tasks]
FROM Q_Tasks_SATRs_OS
GROUP BY Q_Tasks_SATRs_OS.Tax
PIVOT Q_Tasks_SATRs_OS.Partner;

I want to produce a report of the above, which includes totals under each
column. I get a nice report using the wizard, in all respects but for
the
omission of the totals.

I am unsure whether the totals should be generated somewhere in the
query,
or using a formula in a text box in a report. I am conscious that in
theory
the number of columns can be variable depending on the underlying data,
so
my instinct is that it has to be something in the query.

Any help available on this? Sounds like a fairly standard sort of thing
to
want to achieve, but I went round the houses in the Help system without
getting anywhere.
Click to expand...
Click to expand...
 
Edit the last line of the SQL statement like this --
PIVOT Q_Tasks_SATRs_OS.Partner IN ("AAA", "BBB", "CCC", "DDD");

Fill it in for all of your combinations. Then you can do the totals in the
report as all of these collumns will appear every time even if there is no
data for the crosstab query.

Jack Sheet said:
Karl, thank you for taking the trouble to help, and I am so sorry that I
don't understand any of it, as I take it as a kind of duty to try to make it
worth responder's time and effort to respond, at least to understand and
implement it. I don't pretend to understand how my original crosstab query
worked - I just used the Wizard and switched to SQL view to post it here.
The principle was very simple. There are a number of supervisors, listed
under field Qry.Partner, and a larger number of underlings, listed under
field Qry.Tax. Between them they have to undertake tasks, identified by
Qry.ID_Tasks. Each record in Qry represents a single task, and qry.ID_Tasks
contains no duplicates. No underling reports exclusively to any individual
supervisor, but only one underling and only one supervisor controls any
particular task. The crosstab query is supposed to list the supervisors as
column headings, the underlings as row headings, and the total number of
tasks in the middle of the grid, with rows totalled and columns totalled.
So far I get the rows totalled without any problem, as there is a tick box
in the wizard specifically for that purpose. It is just getting the column
totals that is causing the aggravation.

Thus, my example daty in Qry might be:
ID_Tasks.....Partner ......Tax
1..................AAA..........Ted
2..................AAA..........Bill
3.................BBB...........Sam
4.................BBB..........Sam
5..................BBB..........Ben
6..................AAA.........Ben

So far my crosstab query successfully shows

Tax........RowTotal........AAA.......BBB
Ted.................1................1............0
Bill...................1...............1.............0
Sam.................2................0...........2
Ben..................2...............1............1

All I am missing is another row:
ColTotal.........6................3............3
which I am quite happy to appear in a report rather than the source query.

KARL DEWEY said:
What kind of variables are your columns? If you can preset them like in
the
following queries it would solve your problem.
TRANSFORM First(IIf([Name] Is Null,Null,"X")) AS x
SELECT AttendanceTable.Name
FROM AttendanceTable
WHERE (((Format([Present_Date],"yyyymm"))=Format(DateAdd("m",-Val([Enter 0
for this month or 1 for last month]),Date()),"yyyymm")))
GROUP BY AttendanceTable.Name
PIVOT Val(Format([Present_Date],"d")) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

TRANSFORM Count([Troy_s Table2].Temp) AS CountOfTemp
SELECT [Troy_s Table2].Color, [Troy_s Table2].Level
FROM [Troy_s Table2]
GROUP BY [Troy_s Table2].Color, [Troy_s Table2].Level
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And
239.999,"Warm","Hot"));


Jack Sheet said:
Hi all

I have a crosstab query

TRANSFORM Count(Q_Tasks_SATRs_OS.ID_Tasks) AS CountOfID_Tasks
SELECT Q_Tasks_SATRs_OS.Tax, Count(Q_Tasks_SATRs_OS.ID_Tasks) AS [Total
Of
ID_Tasks]
FROM Q_Tasks_SATRs_OS
GROUP BY Q_Tasks_SATRs_OS.Tax
PIVOT Q_Tasks_SATRs_OS.Partner;

I want to produce a report of the above, which includes totals under each
column. I get a nice report using the wizard, in all respects but for
the
omission of the totals.

I am unsure whether the totals should be generated somewhere in the
query,
or using a formula in a text box in a report. I am conscious that in
theory
the number of columns can be variable depending on the underlying data,
so
my instinct is that it has to be something in the query.

Any help available on this? Sounds like a fairly standard sort of thing
to
want to achieve, but I went round the houses in the Help system without
getting anywhere.
Click to expand...
Click to expand...
Click to expand...
 
Had another thought. I don't know if this would work - not yet had a chance
to try it.
But I was thinking I could create two crosstab queries. In the second one I
would reverse the column and row headings compared with the first one. That
way the column totals that I require in the first query would be displayed
as row totals in the second query.


KARL DEWEY said:
Edit the last line of the SQL statement like this --
PIVOT Q_Tasks_SATRs_OS.Partner IN ("AAA", "BBB", "CCC", "DDD");

Fill it in for all of your combinations. Then you can do the totals in
the
report as all of these collumns will appear every time even if there is no
data for the crosstab query.

Jack Sheet said:
Karl, thank you for taking the trouble to help, and I am so sorry that I
don't understand any of it, as I take it as a kind of duty to try to make
it
worth responder's time and effort to respond, at least to understand and
implement it. I don't pretend to understand how my original crosstab
query
worked - I just used the Wizard and switched to SQL view to post it here.
The principle was very simple. There are a number of supervisors, listed
under field Qry.Partner, and a larger number of underlings, listed under
field Qry.Tax. Between them they have to undertake tasks, identified by
Qry.ID_Tasks. Each record in Qry represents a single task, and
qry.ID_Tasks
contains no duplicates. No underling reports exclusively to any
individual
supervisor, but only one underling and only one supervisor controls any
particular task. The crosstab query is supposed to list the supervisors
as
column headings, the underlings as row headings, and the total number of
tasks in the middle of the grid, with rows totalled and columns totalled.
So far I get the rows totalled without any problem, as there is a tick
box
in the wizard specifically for that purpose. It is just getting the
column
totals that is causing the aggravation.

Thus, my example daty in Qry might be:
ID_Tasks.....Partner ......Tax
1..................AAA..........Ted
2..................AAA..........Bill
3.................BBB...........Sam
4.................BBB..........Sam
5..................BBB..........Ben
6..................AAA.........Ben

So far my crosstab query successfully shows

Tax........RowTotal........AAA.......BBB
Ted.................1................1............0
Bill...................1...............1.............0
Sam.................2................0...........2
Ben..................2...............1............1

All I am missing is another row:
ColTotal.........6................3............3
which I am quite happy to appear in a report rather than the source
query.

KARL DEWEY said:
What kind of variables are your columns? If you can preset them like
in
the
following queries it would solve your problem.
TRANSFORM First(IIf([Name] Is Null,Null,"X")) AS x
SELECT AttendanceTable.Name
FROM AttendanceTable
WHERE
(((Format([Present_Date],"yyyymm"))=Format(DateAdd("m",-Val([Enter 0
for this month or 1 for last month]),Date()),"yyyymm")))
GROUP BY AttendanceTable.Name
PIVOT Val(Format([Present_Date],"d")) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

TRANSFORM Count([Troy_s Table2].Temp) AS CountOfTemp
SELECT [Troy_s Table2].Color, [Troy_s Table2].Level
FROM [Troy_s Table2]
GROUP BY [Troy_s Table2].Color, [Troy_s Table2].Level
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And
239.999,"Warm","Hot"));


:

Hi all

I have a crosstab query

TRANSFORM Count(Q_Tasks_SATRs_OS.ID_Tasks) AS CountOfID_Tasks
SELECT Q_Tasks_SATRs_OS.Tax, Count(Q_Tasks_SATRs_OS.ID_Tasks) AS
[Total
Of
ID_Tasks]
FROM Q_Tasks_SATRs_OS
GROUP BY Q_Tasks_SATRs_OS.Tax
PIVOT Q_Tasks_SATRs_OS.Partner;

I want to produce a report of the above, which includes totals under
each
column. I get a nice report using the wizard, in all respects but for
the
omission of the totals.

I am unsure whether the totals should be generated somewhere in the
query,
or using a formula in a text box in a report. I am conscious that in
theory
the number of columns can be variable depending on the underlying
data,
so
my instinct is that it has to be something in the query.

Any help available on this? Sounds like a fairly standard sort of
thing
to
want to achieve, but I went round the houses in the Help system
without
getting anywhere.
Click to expand...
Click to expand...
Click to expand...
 
If you put the IN statement in your crosstab query you can then create a
totals query to insert as a subreport in your main report.

Jack Sheet said:
Had another thought. I don't know if this would work - not yet had a chance
to try it.
But I was thinking I could create two crosstab queries. In the second one I
would reverse the column and row headings compared with the first one. That
way the column totals that I require in the first query would be displayed
as row totals in the second query.


KARL DEWEY said:
Edit the last line of the SQL statement like this --
PIVOT Q_Tasks_SATRs_OS.Partner IN ("AAA", "BBB", "CCC", "DDD");

Fill it in for all of your combinations. Then you can do the totals in
the
report as all of these collumns will appear every time even if there is no
data for the crosstab query.

Jack Sheet said:
Karl, thank you for taking the trouble to help, and I am so sorry that I
don't understand any of it, as I take it as a kind of duty to try to make
it
worth responder's time and effort to respond, at least to understand and
implement it. I don't pretend to understand how my original crosstab
query
worked - I just used the Wizard and switched to SQL view to post it here.
The principle was very simple. There are a number of supervisors, listed
under field Qry.Partner, and a larger number of underlings, listed under
field Qry.Tax. Between them they have to undertake tasks, identified by
Qry.ID_Tasks. Each record in Qry represents a single task, and
qry.ID_Tasks
contains no duplicates. No underling reports exclusively to any
individual
supervisor, but only one underling and only one supervisor controls any
particular task. The crosstab query is supposed to list the supervisors
as
column headings, the underlings as row headings, and the total number of
tasks in the middle of the grid, with rows totalled and columns totalled.
So far I get the rows totalled without any problem, as there is a tick
box
in the wizard specifically for that purpose. It is just getting the
column
totals that is causing the aggravation.

Thus, my example daty in Qry might be:
ID_Tasks.....Partner ......Tax
1..................AAA..........Ted
2..................AAA..........Bill
3.................BBB...........Sam
4.................BBB..........Sam
5..................BBB..........Ben
6..................AAA.........Ben

So far my crosstab query successfully shows

Tax........RowTotal........AAA.......BBB
Ted.................1................1............0
Bill...................1...............1.............0
Sam.................2................0...........2
Ben..................2...............1............1

All I am missing is another row:
ColTotal.........6................3............3
which I am quite happy to appear in a report rather than the source
query.

What kind of variables are your columns? If you can preset them like
in
the
following queries it would solve your problem.
TRANSFORM First(IIf([Name] Is Null,Null,"X")) AS x
SELECT AttendanceTable.Name
FROM AttendanceTable
WHERE
(((Format([Present_Date],"yyyymm"))=Format(DateAdd("m",-Val([Enter 0
for this month or 1 for last month]),Date()),"yyyymm")))
GROUP BY AttendanceTable.Name
PIVOT Val(Format([Present_Date],"d")) In
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31);

TRANSFORM Count([Troy_s Table2].Temp) AS CountOfTemp
SELECT [Troy_s Table2].Color, [Troy_s Table2].Level
FROM [Troy_s Table2]
GROUP BY [Troy_s Table2].Color, [Troy_s Table2].Level
PIVOT IIf([Temp]<90,"Cool",IIf([Temp] Between 90 And
239.999,"Warm","Hot"));


:

Hi all

I have a crosstab query

TRANSFORM Count(Q_Tasks_SATRs_OS.ID_Tasks) AS CountOfID_Tasks
SELECT Q_Tasks_SATRs_OS.Tax, Count(Q_Tasks_SATRs_OS.ID_Tasks) AS
[Total
Of
ID_Tasks]
FROM Q_Tasks_SATRs_OS
GROUP BY Q_Tasks_SATRs_OS.Tax
PIVOT Q_Tasks_SATRs_OS.Partner;

I want to produce a report of the above, which includes totals under
each
column. I get a nice report using the wizard, in all respects but for
the
omission of the totals.

I am unsure whether the totals should be generated somewhere in the
query,
or using a formula in a text box in a report. I am conscious that in
theory
the number of columns can be variable depending on the underlying
data,
so
my instinct is that it has to be something in the query.

Any help available on this? Sounds like a fairly standard sort of
thing
to
want to achieve, but I went round the houses in the Help system
without
getting anywhere.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top