How to get the average of every three records in a column starting from first record in MS Access/SQL?

Question

I am working on something where i am stuck in getting the average of say every three/four/five records starting from first record in a column. If i have a table with data say

ID_Col1 | Value_Col2
1       | 1.5
2       | 2
3       | 2.5
4       | 3
5       | 3.5
6       | 4
7       | 4.5
8       | 5
9       | 5.5
10      | 6

If we say average of every three records then the Output required is

    every_three_records_average_Column
    none
    none
    average(1.5, 2, 2.5)
    average(2, 2.5, 3)
    average(2.5, 3, 3.5)
    average(3, 3.5, 4)
    average(3.5, 4, 4.5)
    average(4, 4.5, 5)
    average(4.5, 5, 5.5)
    average(5, 5.5, 6)

Does anyone have any idea to get this kind of output in SQL query.

Any help would be much appreciated.

Thanks, Honey

Answer 1

SQL Fiddle Demo

SELECT 
     T1.[ID_Col1], T2.[ID_Col1], T3.[ID_Col1],
     T1.[Value_Col2] , T2.[Value_Col2] , T3.[Value_Col2],
     (T1.[Value_Col2] + T2.[Value_Col2] + T3.[Value_Col2])/3

FROM Source T1
JOIN Source T2
  ON T1.[ID_Col1] = T2.[ID_Col1] - 1 
JOIN Source T3
  ON T2.[ID_Col1] = T3.[ID_Col1] - 1 

OUTPUT