468,483 Members | 2,359 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,483 developers. It's quick & easy.

Noob having problems joining two queries in one report Access 2007

17
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?
Oct 12 '13 #1
0 985

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by LadyIlsebet | last post: by
9 posts views Thread by prakashwadhwani | last post: by
reply views Thread by NPC403 | last post: by
1 post views Thread by kmladenovski | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.