I have this Report that displays information based on someone selecting a Start Date and End Date.
The Table is: MSys_obj These are its fields: Incoming/Outgoing,Date,Worker,Caller,Sex,
Time,Duration,Location,Reason,Satisfaction,A,B,C,D ,E,Outcome
And this is its Query (Usysqry_Smiley):
PARAMETERS [Start Date] DateTime, [End Date] DateTime;
SELECT MSys_obj.Date, MSys_obj.Location, MSys_obj.[Incoming/Outgoing], MSys_obj.Duration, MSys_obj.Satisfaction, MSys_obj.A, MSys_obj.B, MSys_obj.C, MSys_obj.D, MSys_obj.E
FROM MSys_obj
WHERE (((MSys_obj.Date) Between [Start Date] And [End Date]));
I now wish to make the report automatically display a breakdown of the number of calls in each of three different segments during the six hour work shift.
This is its Query (Usysqry_Hours):
SELECT "4:30 PM TO 6:29 PM" AS TimeCalc, Count(Q.Time) AS TotalCalls
FROM MSys_obj
WHERE Q.Time >=#4:30 PM#
AND Q.Time <=#6:29 PM#
UNION
SELECT "6:30 PM TO 8:29 PM" AS TimeCalc, Count(Q.Time) AS TotalCalls
FROM MSys_obj
WHERE Q.Time >=#6:30 PM#
AND Q.Time <=#8:30 PM#
UNION SELECT "8:30 PM TO 10:30 PM" AS TimeCalc, Count(Q.Time) AS TotalCalls
FROM MSys_obj
WHERE Q.Time>= #8:30 PM#
AND Q.Time <=#10:30 PM#;
I don't know if this is the correct SQL, or how to make the UNION function work. Nor do I know what formula to place in the Report Footer to display the following additional information in the report to make it also display:
4:30 to 6:29: (count of hours)
6:30 to 8:28: (count of hours)
8:30 to 10:30 (count of hours)
Any ideas as to what I'm doing wrong?