Query with hours

[Peter D]

I have a table with hours in it.
09:00
09:23
09:55
10:05
11:21
11:26
12:03
.......

I would like to make a query that shows me the amount per two hours.


Between 00:00 and 01:59 = 3
between 02:00 and 03:59 = 0
between 04:00 and 05:59 = 6
.........

Do i have to make 12 different query's and/or can it be done ?

 

[Allen Browne]

1. Create a query, and type an expression like this into the Field row:
Segment: DateDiff("h", #0:0:0#, [MyHours]) \ 2
substituting your field name for MyHours.
This shows 0 for the first 2-hours of the day, 1 for the next 2, etc.


2. Depress the Total icon on the toolbar (upper sigma icon).
Access adds a Total row to the design grid.
Accept Group By under the calculated field above:

3. Drag the primary key field into the grid.
In the Total row under this field, choose Count.
This gives the count of records per segment of the day.

If you time field actually contains a date as well as a time, use:
Segment: DateDiff("h", #0:0:0#, TimeValue([MyHours])) \ 2