By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,101 Members | 1,360 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,101 IT Pros & Developers. It's quick & easy.

Noob having problems joining two queries in one report Access 2007

P: 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
Share this question for a faster answer!
Share on Google+

Post your reply

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