Group by more than 1 hour

[robinsoninho]

i have a table of time and dates and a value after it inn which i want to do a query which groups these values by every 3 hours e.g

my table
12/02/2001 00:00 6
12/02/2001 01:00 4
12/02/2001 02:00 8
12/02/2001 03:00 12
12/02/2001 04:00 7
12/02/2001 05:00 6


i want the query to do this
12/02/2001 02:00 18
12/02/2001 05:00 25

i cant find anything anywahere and its use ids for a chart in which the chart wizard allows grouping by 1 hour and not by more than 1 hour any help will be much obliged

cheers

 

[antomack]

My solution depends on how the data is recorded. Looking at the sample data you have given it appears to be an hourley record that is added on the hour. Assuming that date and time are seperate fields and that the time field is in time format hh:nn:ss you could try the following.

1) Create a table for the 8 3-hour groups that you are using as below
TimeGroup StartTime EndTime
02:00 00:00:00 02:00:00
05:00 03:00:00 05:00:00
08:00 06:00:00 08:00:00
11:00 09:00:00 11:00:00
14:00 12:00:00 14:00:00
17:00 15:00:00 17:00:00
20:00 18:00:00 20:00:00
23:00 21:00:00 23:00:00

2) Using this table and your data table create a group query with your date field, the TimeGroup field, your number field and your Time field. Get the sum of you number field and set where on the Time field with criteria of
Between [StartTime] AND [EndTime]

If your records are not stored on an hourly basis at the hour then the table in 1 will need to change to something like
TimeGroup StartTime EndTime
02:00 00:00:00 02:00:00
05:00 02:00:01 05:00:00
08:00 05:00:01 08:00:00
11:00 08:00:01 11:00:00
14:00 11:00:01 14:00:00
17:00 14:00:01 17:00:00
20:00 17:00:01 20:00:00
23:00 20:00:01 23:59:59