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
--
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
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
since the StartDate can be calculated.
PARAMETERS [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
GROUP BY qryDISR_ReportA.Svc_Type, qryDISR_ReportA.maploc
PIVOT "D" &
DateDiff("D",[Log_Time],[Forms]![frmWhatDates5]![EndDate])
IN ("D0","D1","D2","D3",....,"D30");
The "D0","D1","D2","D3",....,"D30" can be edited in your
Column
Headings
property and must include all 31 values.
Your report now can use columns
"D0","D1","D2","D3",....,"D30"
which
can
easily be totaled.
--
Duane Hookom
MS Access MVP
--
message
The SQL for the crosstab query is as follows:
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 Format([Log_Time],"mm/dd/yy");
--
KC
:
That particular method is slow and inflexible. Consider
reviewing
the
method
in the Crosstab.mdb available for download at
http://www.invisibleinc.com/divFiles.cfm?divDivID=4.
What is the SQL for your crosstab? There may be an even
simpler
method.
--
Duane Hookom
MS Access MVP
message
I have a report based on a crosstab query. It
dynamically
formats
the
rows
and columns (from article 328320 on the Microsoft
site)
to
accept
up
to
31
days based on user input. The query outputs only
three
fields:
service
type,
area, and a calculated field which is the number of
service
orders
per
day
for each service typa and area. All works fine,
except
my
client
wants
subtotals for each area. None of the fields in the
report are
bound,
so
whenever I try to add a group footer with total field
(e.g.,
data
source=Sum(clo3)...) I get erroneous results. Is
there
any
way
to
add
the
subtotals directly to the report, or must it be done
programatically,
and
if
so how? Thanks a lot.
