Showing what part of the day is most busy

[rockyjr]

I use a database that tracks calls answered during the day. I have a fielded called "opened date". What I want to do is to show what part of the day is most busy.

Ex: between 8am to 9am (how many calls where answered)
between 9am to 10am (how many calls where answered) and so on....

I dont mind if it is done through a report or through a form.

Can someone show me the direction on how I could accomplish this?

 

[gemma-the-husky]

theres an underused function called PARTITION

does exactly what you want - splits your data into equal sized chunks of information

try it in a query

 

[raskew]

Agree with Gemma that the Partition() function is a good option.

Here's another possibility:

SELECT
    Format(TimeValue([starttime]),"hh") AS Expr2
  , Count(Format(TimeValue([starttime]),"hh")) AS Expr1
FROM
   tblDevProcess1
WHERE
   ((Not (tblDevProcess1.startTime) Is Null))
GROUP BY
   Format(TimeValue([starttime]),"hh")
HAVING
   (((Format(TimeValue([starttime]),"hh"))<>"00"));

To use, assuming that you are using a valid data/time data type, copy/paste to a new query, replace table (tblDevProcess1) and field ([starttime]) names as appropriate.

If your date/time field doesn't contain nulls (mine did) you can eliminate the WHERE condition. Likewise, if you're certain that each date/time includes a valid time, remove the HAVING condition.

I had to include these because of inaccuracies in my test table.

HTH - Bob

 

[rockyjr]

Thank you gemma-the-husky and raskew. That helped a lot.

With the SELECT format, is there a way to select a specific day (from and to a certain date)?

I'm assuming that it can be done with the code, but , I'm looking to select a date from a text field on a form. Which would change the query.