grand total on crosstab columns?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

newbie.

Hi, I don't know if this is possible and if it is I
imagine there's a better way to do it than I'm trying.
The following crosstab query produces the results in the
sudo table below it. Is there anyway to total the date
columns (under the asterix line below)?

TRANSFORM Sum(xresources.days_required) AS Days_required
SELECT xresources.job_name, Max(xresources.priority) AS
MaxPriority, Min(xresources.flexability) AS
MinFlexability, xresources.phase
FROM xresources
GROUP BY xresources.phase, xresources.job_name
ORDER BY xresources.job_name, xresources.phase
PIVOT Format([date],'Short Date') IN (1/1/2003, 1/2/2003,
1/3/2003, 1/4/2003, 1/5/2003);

job_name..Priority..Flexability..phase..1/1/03..1/2/03
Bacon.....5.........1............Sales..1
DeFrances.2.........1............Sales..1.......1
**************************************************
Daily Totals............................2.......1
 
Use "relative" dates rather than absolute dates in you column headings
expression. This solution requires no code.
It looks like your report will contain 5 days and assume an ending date of
Forms!frmA!txtEndDate.
ColHead:"Day" & DateDiff("d",[Date], Forms!frmA!txtEndDate)
Set the Column Headings property to
"Day0","Day1",..."Day4"
Day0 will be the ending date and Day4 will be four days earlier. Your report
and query will always have the same columns/fields. In your report, you can
use text boxes as column labels:
=DateAdd("d",0,Forms!frmA!txtEndDate)
=DateAdd("d",-1,Forms!frmA!txtEndDate)
=DateAdd("d",-2,Forms!frmA!txtEndDate)
=DateAdd("d",-3,Forms!frmA!txtEndDate)
=DateAdd("d",-4,Forms!frmA!txtEndDate)
Set the query parameters by selecting Query|Parameters and entering
Forms!frmA!txtEndDate Date/Time

Summing these field is like summing any other fields.
 
Thanks Duane. I think I follow. To clarify, I have to run
a report on top of the query in order to get the totals?
Also, the cross tab query here is a code generated query
based on an open range form selection - they select start
date and end date and I haven't specified a range limit
though I could if required, as you say, 5 days ending on
such and such.
But if I have to generate a report anyway - well can you
generate a report programmatically? Then couldn't I leave
the range open? Or do you have to set up a report that
willa always fit the data?

thanks for your help,
jb
-----Original Message-----
Use "relative" dates rather than absolute dates in you column headings
expression. This solution requires no code.
It looks like your report will contain 5 days and assume an ending date of
Forms!frmA!txtEndDate.
ColHead:"Day" & DateDiff("d",[Date], Forms!frmA! txtEndDate)
Set the Column Headings property to
"Day0","Day1",..."Day4"
Day0 will be the ending date and Day4 will be four days earlier. Your report
and query will always have the same columns/fields. In your report, you can
use text boxes as column labels:
=DateAdd("d",0,Forms!frmA!txtEndDate)
=DateAdd("d",-1,Forms!frmA!txtEndDate)
=DateAdd("d",-2,Forms!frmA!txtEndDate)
=DateAdd("d",-3,Forms!frmA!txtEndDate)
=DateAdd("d",-4,Forms!frmA!txtEndDate)
Set the query parameters by selecting Query|Parameters and entering
Forms!frmA!txtEndDate Date/Time

Summing these field is like summing any other fields.

--
Duane Hookom
MS Access MVP


newbie.

Hi, I don't know if this is possible and if it is I
imagine there's a better way to do it than I'm trying.
The following crosstab query produces the results in the
sudo table below it. Is there anyway to total the date
columns (under the asterix line below)?

TRANSFORM Sum(xresources.days_required) AS Days_required
SELECT xresources.job_name, Max(xresources.priority) AS
MaxPriority, Min(xresources.flexability) AS
MinFlexability, xresources.phase
FROM xresources
GROUP BY xresources.phase, xresources.job_name
ORDER BY xresources.job_name, xresources.phase
PIVOT Format([date],'Short Date') IN (1/1/2003, 1/2/2003,
1/3/2003, 1/4/2003, 1/5/2003);

job_name..Priority..Flexability..phase..1/1/03..1/2/03
Bacon.....5.........1............Sales..1
DeFrances.2.........1............Sales..1.......1
**************************************************
Daily Totals............................2.......1
Click to expand...


.
Click to expand...
 
I would limit the number of dates to a specific number. Dynamically creating
a report seems like too much work when the single crosstab query and one
report work quite efficiently.

--
Duane Hookom
MS Access MVP


jb33 said:
Thanks Duane. I think I follow. To clarify, I have to run
a report on top of the query in order to get the totals?
Also, the cross tab query here is a code generated query
based on an open range form selection - they select start
date and end date and I haven't specified a range limit
though I could if required, as you say, 5 days ending on
such and such.
But if I have to generate a report anyway - well can you
generate a report programmatically? Then couldn't I leave
the range open? Or do you have to set up a report that
willa always fit the data?

thanks for your help,
jb
-----Original Message-----
Use "relative" dates rather than absolute dates in you column headings
expression. This solution requires no code.
It looks like your report will contain 5 days and assume an ending date of
Forms!frmA!txtEndDate.
ColHead:"Day" & DateDiff("d",[Date], Forms!frmA! txtEndDate)
Set the Column Headings property to
"Day0","Day1",..."Day4"
Day0 will be the ending date and Day4 will be four days earlier. Your report
and query will always have the same columns/fields. In your report, you can
use text boxes as column labels:
=DateAdd("d",0,Forms!frmA!txtEndDate)
=DateAdd("d",-1,Forms!frmA!txtEndDate)
=DateAdd("d",-2,Forms!frmA!txtEndDate)
=DateAdd("d",-3,Forms!frmA!txtEndDate)
=DateAdd("d",-4,Forms!frmA!txtEndDate)
Set the query parameters by selecting Query|Parameters and entering
Forms!frmA!txtEndDate Date/Time

Summing these field is like summing any other fields.

--
Duane Hookom
MS Access MVP


newbie.

Hi, I don't know if this is possible and if it is I
imagine there's a better way to do it than I'm trying.
The following crosstab query produces the results in the
sudo table below it. Is there anyway to total the date
columns (under the asterix line below)?

TRANSFORM Sum(xresources.days_required) AS Days_required
SELECT xresources.job_name, Max(xresources.priority) AS
MaxPriority, Min(xresources.flexability) AS
MinFlexability, xresources.phase
FROM xresources
GROUP BY xresources.phase, xresources.job_name
ORDER BY xresources.job_name, xresources.phase
PIVOT Format([date],'Short Date') IN (1/1/2003, 1/2/2003,
1/3/2003, 1/4/2003, 1/5/2003);

job_name..Priority..Flexability..phase..1/1/03..1/2/03
Bacon.....5.........1............Sales..1
DeFrances.2.........1............Sales..1.......1
**************************************************
Daily Totals............................2.......1
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top