Count instances of records at each hourly interval within a selected date range

[Amras]

Good afternoon, gentlefolk. I thank you for taking a moment of your time to examine my question.



I've got a single table with multiple fields, three of which are a date field ('DDate'), a time field ('TimeET') and a unique identifying field ('UniqueCallKey').  I'm attempting to write a query in the QBE that will allow me to count the number of instances of 'UniqueCallKey' for each hourly time interval (7:00:00 AM - 7:59:59 AM, 8:00:00 AM - 8:59:59 AM, 9:00:00 - 9:59:59 AM, etc) for any selected date range (BETWEEN 'DDate'(1) AND 'DDate'(2)).  When I try to simply use the Count function on 'UniqueCallKey' as an Expression and 'TimeET' with 'Like '7:*AM' as a Where criteria then do the same with another instance of 'UniqueCallKey' and 'Like 8:*AM' as the criteria for a second Where criteria for 'TimeET' the query returns an empty set.  What I'm trying to accomplish would be a column of dates, a second column that counts the number of instances of 'UniqueCallKey' at the 7AM interval for each date in column 1, a third column that counts the number of instances of 'UniqueCallKey' at the 8AM interval for each date in column 1, etc to a final column for counts at 7PM.

If this has been previously asked and answered, I apologize but I wasn't able to find anything that would help me.

I certainly appreciate any assistance you might be able to provide. If I haven't been clear, please respond with any questions you might have and I'll be happy to expand on anything.

[June7]

Try extracting the hour from each time value and using that constructed field for grouping records.

Hour([TimeET]) AS Hr

The output is 24-hour clock hour.

Then it looks like you need a CROSSTAB query, The Hr field would be the Column Header parameter.

[Amras]

You're the BEST, June7! I'd never used a Crosstab query before, so wasn't aware of it's abilities. Thanks so much for your help!

[June7]

They can also be very frustrating. Because of their dynamic nature, can be difficult to build a stable report to run perpetually. Review http://allenbrowne.com/ser-67.html