Count number of Items in a Time/Date Range

[Miguel Valenzue]

I want to count the number of items that occur within a range of time throughout different days.
My original data comes from a machine that logs the time a car passes a point in a road over different days (Traffic Analysis).
I'd like to create a query that would count the number of cars between certain time periods, i.e from 7 to 10, 10 to 14:00 and 14:00 to 21:00 (military time).
Currently the date field is displayed as such
(23.08.2010 10:37:20)
(day, month, year, hour, minute, second)
I have created a seperate table named Data_Range with a the following Columns: Start_Time, End_Time, ID
It would be nice if I could write a query or VB script that would look at the time ranges specified in Data_Range and then give me the resulting count for items within that time range regardless of date.

I'm not sure of the exact SQL syntax but I envision it to be something like this:

SELECT Time from TrafficDataTable, Count* as Number_of_cars WHERE Time_1 is between Start_time and End_Time

Thanks
Miguel

[TheSmileyCoder]

Hi again Miguel

I think you should be able to use TimeValue([Time]) to extract the time part of the date/time field, and then make a query like so:

SELECT Data_Range.TimeStart, Data_Range.TimeEnd, Count(TrafficDataTable.*) AS NrOfCars
FROM Data_Range, TrafficDataTable
WHERE (((TimeValue(TrafficDataTable.Time)) Between [TimeStart] And [TimeEnd]))
GROUP BY Data_Range.TimeStart, Data_Range.TimeEnd;
 

[Miguel Valenzue]

Thanks again Smiley.
I had originally tried this:

SELECT COUNT(Datotid) AS BilNumber from tbl_KNIVEVEIEN WHERE (DatePart('h',Datotid) BETWEEN 7 and 19)
UNION
SELECT COUNT(Datotid) AS BilNumber from tbl_KNIVEVEIEN WHERE (DatePart('h',Datotid) BETWEEN 19 and 23)
UNION
SELECT COUNT(Datotid) AS BilNumber from tbl_KNIVEVEIEN WHERE (DatePart('h',Datotid) BETWEEN 23 and 7)
UNION
SELECT COUNT (Datotid) AS BilNumber from tbl_KNIVEVEIEN;
 

using DatePart.
The interesting thing is that when I go from 23 to 7, it counts from 7 to 23, so I'll have to probably do some ranges that stay within the 24 hour increments and then add them or something.

[TheSmileyCoder]

I think maybe you could try instead of:

BETWEEN StartTime and EndTime

to use

Time>StartTime AND Time<EndTime