Average of data in specific time periods

[boerbende]

Dear readers

I have got a table with MULTIPLE measurements on MULTIPLE different times. Example
Date ; time ; Position ; Measurement
1-2 ; 01:00 ; 12 ; 21
1-2 ; 02:20 ; 12 ; 21
1-2 ; 06:50 ; 12 ; 24
1-2 ; 09:30 ; 12 ; 18
1-2 ; 16:20 ; 12 ; 19
1-2 ; 18:50 ; 12 ; 21

(example is only one position, one day, but I have actually over 10.000 positions and measurements can be up to 3600 / hour, and I will to look into several years of data. Just to indicate the amount of data)

To work out the data, I need first to look at this table statistically.
So I think about an average in specific time ranges:

Avg(measurement) from 00:00 - 07:59. Result: 22 (=(21+21+24)/3)
Avg(measurement) from 08:00 - 15:59. Result: 18 (=18/1)
Avg(measurement) from 16:00 - 24:00. Result: 20 (=(19+21)/2)

I know how to do this in VBA, but is there a query which can do this for me?

Thanks for thinking with me

Ben

 

[arnelgp]

not tested:

Avg(IIF(Format[Time],"hhmm")>="0000" AND Format([Time],"hhmm")<="0759", [Measure], Null)) AS 0000-0759, Avg(IIF(Format[Time],"hhmm")>="0800" AND Format([Time],"hhmm")<="1559", [Measure], Null)) AS 0800-1559, Avg(IIF(Format[Time],"hhmm")>="1600" AND Format([Time],"hhmm")<="2400", [Measure], Null)) AS 1600-2400 FROM table;

 

[boerbende]

Tested it and conclusion is that I don't need VBA! Thanks for your suggestion arnelGP.

 

[arnelgp]

that was the avg of all your records, if you want to do avg by date:

SELECT table.[Date],Avg(IIF(Format[Time],"hhmm")>="0000" AND Format([Time],"hhmm")<="0759", [Measure], Null)) AS 0000-0759, Avg(IIF(Format[Time],"hhmm")>="0800" AND Format([Time],"hhmm")<="1559", [Measure], Null)) AS 0800-1559, Avg(IIF(Format[Time],"hhmm")>="1600" AND Format([Time],"hhmm")<="2400", [Measure], Null)) AS 1600-2400 FROM table GROUP by table.[Date];