Query to count time range

[NoClue]

I need to count the number of calls between a time range. Data would need
to look like this:

Date Time
1/10/08 07:38
1/11/08 07:15
1/12/08 08:47

Time # of calls
07:00-07:59 2
08:00-08:59 1

 

[John Spencer]

SELECT DatePart("h",[TableName].[Time]) as HourNumber
, Count([TableName].[Time]) as NumCalls
FROM [TableName]
GROUP BY DatePart("h",[TableName].[Time])

In the query grid
-- add your table
-- add the time field twice
-- Modify the first to read
Field: HourNumber: DatePart("h",[Time])
-- Select View: Totals from the menu
-- Change Group by to Count other the other Time field

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Michael Gramelspacher]

I need to count the number of calls between a time range. Data would need
to look like this:

Date Time
1/10/08 07:38
1/11/08 07:15
1/12/08 08:47

Time # of calls
07:00-07:59 2
08:00-08:59 1

SELECT DATEADD("n", (DATEDIFF("n", 0, T.[Time])\60)*60, 0) AS [Hour Start],
DateAdd("n",59,DATEADD("n", (DATEDIFF("n", 0, T.[Time])\60)*60, 0)) AS [Hour
End],
Count (*) AS [# of Calls]
FROM Table1 AS T
GROUP BY (DATEDIFF("n", 0, T.[Time])\60)*60;

Hour Start Hour End # of Calls
7:00:00 AM 7:59:00 AM 2
8:00:00 AM 8:59:00 AM 1

This is kind of what you want, but there are probably easier ways.

 

[KARL DEWEY]

Create a table named CountNumber and field CountNUM filled with numbers from
0 (zero) to 100. Use the query below ---
SELECT Format([CountNUM]*0.0416666666667,"Short Time") & " : " &
Format(([CountNUM]+1)*0.0416666666667,"Short Time") AS [Time Period],
Count(NoClue.CallDateTime) AS [Call Count]
FROM NoClue, CountNumber
WHERE ((([CallDateTime]-Int([CallDateTime])) Between
[CountNUM]*0.0416666666667 And ([CountNUM]+1)*0.0416666666667))
GROUP BY Format([CountNUM]*0.0416666666667,"Short Time") & " : " &
Format(([CountNUM]+1)*0.0416666666667,"Short Time");

 

[Michael Gramelspacher]

I need to count the number of calls between a time range. Data would need
to look like this:

Date Time
1/10/08 07:38
1/11/08 07:15
1/12/08 08:47

Time # of calls
07:00-07:59 2
08:00-08:59 1

And just to leave no stone unturned and building on John's query:

Table2
---------
Hour_Nbr Hour_Name
0 12:00-12:59 AM
1 01:00-01:59 AM
2 02:00-02:59 AM
3 03:00-03:59 AM
4 04:00-04:59 AM
5 05:00-05:59 AM
6 06:00-06:59 AM
7 07:00-07:59 AM
8 08:00-08:59 AM
9 09:00-09:59 AM
10 10:00-10:59 AM
11 11:00-11:59 AM
12 12:00-12:59 PM
13 01:00-01:59 PM
14 02:00-02:59 PM
15 03:00-03:59 PM
16 04:00-04:59 PM
17 05:00-05:59 PM
18 06:000-6:59 PM
19 07:00-07:59 PM
20 08:00-08:59 PM
21 09:00-09:59 PM
22 10:00-10:59 PM
23 11:00-11:59 PM

SELECT T1.Hour_Name AS [Time],
COUNT(* ) AS [# of Calls]
FROM Table1 AS T,
Table2 AS T1
WHERE T1.Hour_Nbr = DATEPART("h",T.[Time])
GROUP BY DATEPART("h",T.[Time]),T1.Hour_Name;

Time # of Calls
07:00-07:59 AM 2
08:00-08:59 AM 1