Hi =96 Similar to a re****t that contains a graph that plots the total
number of incidents per month, I=92d like to make a graph that plots the
total number of student incidents per day but also per week in a
month. The crosstabs below do the basic grouping of incidents in
months and on weekdays, but users need to see, in a graph, M, T, W, R,
and F of the first week, M, T, W, R, and F of the second week, M, T,
W, R, and F of the third week, M, T, W, R, and F of the fourth week,
and any remaining days in a fifth week in a month. Better yet, it
would be good if the re****t=92s graph only graphed daily results up to
the current day=97instead of showing future days with null records. Is
this possible??
The "IncidentDateTime" field is a date/time stamp with the General
date format; including minutes.
=91Crosstab to total incidents per month=85
TRANSFORM Count(qryRe****tAll.IncidentID) AS CountOfIncidentID
SELECT qryRe****tAll.StudentID, IIf(IsNull([OName]),[LName] & ", " &
Left([FName],1) & ".",[LName] & ", " & Left([OName],1) & ".") AS
ExpCombinedName, Count(qryRe****tAll.IncidentID) AS [Total Of
IncidentID]
FROM qryRe****tAll
GROUP BY qryRe****tAll.StudentID, qryRe****tAll.LName,
qryRe****tAll.FName, qryRe****tAll.OName, IIf(IsNull([OName]),[LName] &
", " & Left([FName],1) & ".",[LName] & ", " & Left([OName],1) & ".")
PIVOT Format([IncidentDateTime],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
=91Crosstab to total incidents per day=85
TRANSFORM Count(qryRe****tAll.IncidentID) AS CountOfIncidentID
SELECT qryRe****tAll.StudentID, IIf(IsNull([OName]),[LName] & ", " &
Left([FName],1) & ".",[LName] & ", " & Left([OName],1) & ".") AS
ExpCombinedName, Count(qryRe****tAll.IncidentID) AS [Total Of
IncidentID]
FROM qryRe****tAll
GROUP BY qryRe****tAll.StudentID, qryRe****tAll.LName,
qryRe****tAll.FName, qryRe****tAll.OName, IIf(IsNull([OName]),[LName] &
", " & Left([FName],1) & ".",[LName] & ", " & Left([OName],1) & ".")
PIVOT Format([IncidentDateTime],"dddd") In
('Monday','Tuesday','Wednesday','Thursday','Friday');
Hope this makes sense. Thanks for any insight.
|
