Subtotals in Crosstab Report

[Guest]

It seems to work fine, and the report looks great. Thanks so much for all
your help.
--
KC

I'm not sure where you got "=EndDate" and "=EndDate-1". This wasn't from my
replies.

--
Duane Hookom
MS Access MVP
--

I'm having success with the report as it is, and am able to refer to
EndDate
for column labels (e.g., =EndDate, =DndDate-1,...). I have edited the SQL
to
create a "weekly" query and report with D0...D6. Thank you.
--
KC

Don't use any code in your report. Bind your fields across your report's
detail section how you want them to appear. For column headings/labels,
use
text boxes with control sources like:

=DateAdd("d",0,[Forms]![frmWhatDates5]![EndDate])
=DateAdd("d",-1,[Forms]![frmWhatDates5]![EndDate])
=DateAdd("d",-2,[Forms]![frmWhatDates5]![EndDate])
=DateAdd("d",-3,[Forms]![frmWhatDates5]![EndDate])
... etc ...
=DateAdd("d",-30,[Forms]![frmWhatDates5]![EndDate])
Make sure the proper text box is above the appropriate column in your
report.
--
Duane Hookom
MS Access MVP
--

I just used the previous (dynamic format) report as a template. I still
would have to reverse the order of the columns, though. in other words
D30=day 1, D29=day 2 . . .D0=Day 31. Thanks.
--
KC


:

Why are you using unbound controls? That seems like a lot of work to
go
through when your columns are defined/static.

--
Duane Hookom
MS Access MVP
--

I was using unbound controls on the report with control source = D0,
D1,
...so I just reversed the order and all looks fine now. Thank you
so
much
for your patience and help.
--
KC


:

The name of the column shouldn't make any difference. You don't
display
the
name of the column in the report so a name isn't significant.

--
Duane Hookom
MS Access MVP
--

I just realized that my report totals are reversed from my
previous
report
(before I began talking with you). The SQL you gave me is
reversing
the
date
order for counting service orders, i.e., D0=Jan31 and D30=Jan1.
All
the
data
looks good, just in reverse order. What do I need to do to
re-reverse
it?
THANK YOU.
--
KC


:

The formatting with this new SQL works perfectly. I didn't even
have
to
use
the totals from the query. With the zero values produced by the
query
for
D0, D1, ...when there were no service orders to count, my
unbound
row
and
report footer total cels produced the correct numbers.

However, I now recognize a problem with the data. For some
reason
the
query
counts a service order for Jan 3rd as having been done on Jan
1st.
The
report totals for service orders in Jan. are correct, but the
daily
column
totals are off. That is, the query is showing counts for
service
orders
on
the wrong day. I'm looking at the old SQL to see how the dates
are
handeled
differently in the new. I am so close, thanks to your help.
Just
need
to
get this last ironed out. Thank you.
--
KC


:

Try this SQL:
PARAMETERS [Forms]![frmWhatDates5]![EndDate] DateTime;
TRANSFORM Val(Nz(Count(SO_Number),0)) AS CountOfSO_Number
SELECT Svc_Type AS [Service Type], maploc AS Area,
Count(SO_Number) as [Count SOs]
FROM qryDISR_ReportA
WHERE
Log_Time>=DateAdd("d",-30,[Forms]![frmWhatDates5]![EndDate])
And
Log_Time<=[Forms]![frmWhatDates5]![EndDate]
GROUP BY Svc_Type, maploc
PIVOT "D" &
DateDiff("D",[Log_Time],[Forms]![frmWhatDates5]![EndDate])
IN ("D0","D1","D2","D3","D4","D5",
"D6","D7","D8","D9","D10","D11",
"D12","D13","D14","D15","D16","D17",
"D18","D19","D20","D21","D22","D23",
"D24","D25","D26","D27","D28","D29","D30");

This sql should provide "row" totals. You should be able to
create
report
totals of columns in the same way you created group totals:
=Sum([Count SOs])
=Sum([D20])
etc

--
Duane Hookom
MS Access MVP
--

message
This is the SQL. It works fine, and I can still use my date
filter
form
with
from and to dates.

PARAMETERS [Forms]![frmWhatDates5]![StartDate] DateTime,
[Forms]![frmWhatDates5]![EndDate] DateTime;
TRANSFORM Count(qryDISR_ReportA.SO_Number) AS
CountOfSO_Number
SELECT qryDISR_ReportA.Svc_Type AS [Service Type],
qryDISR_ReportA.maploc
AS
Area
FROM qryDISR_ReportA
WHERE
(((qryDISR_ReportA.Log_Time)>=[Forms]![frmWhatDates5]![StartDate]
And
(qryDISR_ReportA.Log_Time)<=[Forms]![frmWhatDates5]![EndDate]))
GROUP BY qryDISR_ReportA.Svc_Type, qryDISR_ReportA.maploc
PIVOT "D" &
DateDiff("D",[Log_Time],[Forms]![frmWhatDates5]![EndDate])
IN
("D0","D1","D2","D3","D4","D5","D6","D7","D8","D9","D10","D11","D12","D13","D14","D15","D16","D17","D18","D19","D20","D21","D22","D23","D24","D25","D26","D27","D28","D29","D30");

I am able to total each "D..." in the group footer so I get
subtotals
for
each group, but I would like to total each line within each
group,
and
have
tried to do so with an unbound text box. I also would like
to
add
a
total
to
the report footer that sums each column. Thanks.
--
KC


:

As per my previous reply "What is your complete SQL"?

--
Duane Hookom
MS Access MVP


message
I'm having trouble creating a totals column for each line
within
each
group,
and a total for each column in the report footer. I try
unbound
text
boxes
with something like =Sum or =[D0]+[D1}... but have had no
success.
I've
also
tried editing the query to add a totals column, but with
no
success
there
either. Also, I would like any null values to print as a
zero.
Any
suggestions? Thank you.
--
KC


:

Did you modify the sql to add all the date column
headings
as
I
suggested?
What is your complete SQL and what is the error message?

--
Duane Hookom
MS Access MVP


message
Thanks for the help. I pasted your SQL into my
existing
query
(deleting
the
existing) and got a syntax error message.???
--
KC


:

I would decide on the number of columns/dates that
you
want
to
display.
Assuming you want to display 31 dates you then would
only
need
the
EndDate