How do I create an Average table

**ADezii** · Jul 29 '10, 12:37 PM

Originally posted by izharmel

Hi,
I am trying to create a function to receive a table with a changing number of columns (for each time the function is called).
The first column is always time, in the format "dd/mm/yyyy hh:nn:ss". Every ten seconds there is a new row with many columns of numerical data.
The user writes a number of minutes in a text box, for instance 10,
and the function returns a table containing all the original columns but the values would be averaged by ten minutes (less rows of course)

Thanks in advance,

Izhar

Kindly post some concrete data as well as the results that you would like to see after averaging. What exactly do you mean by Averaging by 10 Minutes?

**izharmel** · Jul 29 '10, 01:02 PM

OK, the original table would look like:

15/06/2010 12:31:00 1970 151.7 2360 116.8
15/06/2010 12:31:10 2000 163.4 2370 127.5
15/06/2010 12:31:20 1990 142.8 2360 112.7
15/06/2010 12:31:30 1990 142.8 2360 109.4
15/06/2010 12:31:40 1960 144.3 2360 110.7
15/06/2010 12:31:50 1950 157.2 2360 121.5
15/06/2010 12:32:00 1940 155.8 2360 122
.
.
.

and the the result table would look like that:

15/06/2010 12:31:00 1990 150.7 2340 114.2
15/06/2010 12:41:10 2020 167.4 2390 187.9
15/06/2010 12:51:20 1700 145.8 2330 165.4
.
.
.

where the values would be averages of the original list, upon periods of ten minutes.

What I've done so far is try to create a record set of the original data, and each time take ten minutes worth of data (using DateAdd) into another rs. then use AVG (SQL) but I don't know how to properly write this, or how to run a query without knowing the name of the column.

Thanks in advance
Izhar

**ADezii** · Jul 29 '10, 02:24 PM

Originally posted by izharmel

OK, the original table would look like:

15/06/2010 12:31:00 1970 151.7 2360 116.8
15/06/2010 12:31:10 2000 163.4 2370 127.5
15/06/2010 12:31:20 1990 142.8 2360 112.7
15/06/2010 12:31:30 1990 142.8 2360 109.4
15/06/2010 12:31:40 1960 144.3 2360 110.7
15/06/2010 12:31:50 1950 157.2 2360 121.5
15/06/2010 12:32:00 1940 155.8 2360 122
.
.
.

and the the result table would look like that:

15/06/2010 12:31:00 1990 150.7 2340 114.2
15/06/2010 12:41:10 2020 167.4 2390 187.9
15/06/2010 12:51:20 1700 145.8 2330 165.4
.
.
.

where the values would be averages of the original list, upon periods of ten minutes.

What I've done so far is try to create a record set of the original data, and each time take ten minutes worth of data (using DateAdd) into another rs. then use AVG (SQL) but I don't know how to properly write this, or how to run a query without knowing the name of the column.

Thanks in advance
Izhar

I've created a Generic Code Template for you in the form of a Public Function. Simply pass to the Function the Name of a valid Table, Query, or SQL Statement. The Function will then calculate the Average of every Column in the Data Source 'except' the first Column. The Results will be displayed in the Debug Window as each Field Name (except the 1st), along with the Averages for those Fields. I've included basic Error Handling, but for the sake of brevity and simplicity, I'll leave the Validating and Formatting issues for you to resolve such as: making sure all Field Values are Numeric in nature, handling of NULLs, Formatting Averages to the proper number of decimal places, etc. The code is easily adaptable, and hopefully should point you in the right direction. Good Luck.

Code Example:

Code:

Public Function fCalcAveragesInTableValues(strTableName As String)
On Error GoTo Err_fCalcAveragesInTableValues
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim lngNumOfRecs As Long
Dim intFldCtr
Dim lngRunningTotal
Dim strFldName As String

If strTableName = "" Then Exit Function

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strTableName, dbOpenSnapshot)

rst.MoveLast: rst.MoveFirst

lngNumOfRecs = rst.RecordCount
If lngNumOfRecs = 0 Then Exit Function

With rst
  'Iterate all the Fields 'except' the 1st Field
  For intFldCtr = 1 To .Fields.Count - 1
    strFldName = .Fields(intFldCtr).Name
      Do While Not .EOF
        lngRunningTotal = lngRunningTotal + .Fields(intFldCtr).Value
          .MoveNext
      Loop
       Debug.Print strFldName & " ==> " & lngRunningTotal / lngNumOfRecs
        lngRunningTotal = 0       'Must Reset after each Iteration
        .MoveFirst
  Next
End With

rst.Close
Set rst = Nothing

Exit_fCalcAveragesInTableValues:
  Exit Function

Err_fCalcAveragesInTableValues:
  If Err.Number = 3078 Then
    MsgBox "The Table [" & strTableName & "] does not exist in the Database", _
            vbCritical, "Invalid Reference"
  Else
    MsgBox Err.Description, vbExclamation, "Error in fCalcAveragesInTableValues()"
  End If
    Resume Exit_fCalcAveragesInTableValues
End Function

Sample Call to Function ignoring any Return Value:
Code:
```
Call fCalcAveragesInTableValues("Table1")
```
Sample results based on Table1 containing 6 Fields, all LONG INTEGERS except the First Field:
Code:
```
Field2 ==> 400
Field3 ==> 8
Field4 ==> 3000
Field5 ==> 21
Field6 ==> 33.3333333333333
```

**izharmel** · Aug 1 '10, 02:32 PM

Thank you very much,
I'll integrate it into what I already have and I'll let you know how it goes.

**izharmel** · Aug 2 '10, 07:09 AM

creating a sub-record set from an existing one

Hi,
I'm trying to combine your function into what I already have.
So far I have a record set with all the information I want.
I run a do while loop on it, to to take a 10 minute segment each time (for example) and pass it on to your function.

Code:

Do While Not rs.EOF
        rs.MoveNext
        'add num of minutes requested to the current time
        time1 = DateAdd("n", AvNum, rs!Time)  
        SQLrs = "SELECT * FROM rs WHERE rs!Time < time1"            
        'take a segment of data to a new rs
        Set rsTemp = db.OpenRecordset(SQLrs, dbOpenSnapshot)

the SQLrs part is wrong...
How do I take a part of a record set into a new one?

Thanks in advance,
Izhar

**ADezii** · Aug 2 '10, 01:03 PM

Originally posted by izharmel

Hi,
I'm trying to combine your function into what I already have.
So far I have a record set with all the information I want.
I run a do while loop on it, to to take a 10 minute segment each time (for example) and pass it on to your function.

Code:

Do While Not rs.EOF
        rs.MoveNext
        'add num of minutes requested to the current time
        time1 = DateAdd("n", AvNum, rs!Time)  
        SQLrs = "SELECT * FROM rs WHERE rs!Time < time1"            
        'take a segment of data to a new rs
        Set rsTemp = db.OpenRecordset(SQLrs, dbOpenSnapshot)

the SQLrs part is wrong...
How do I take a part of a record set into a new one?

Thanks in advance,
Izhar

You included a Pointer to the Recordset (rs) in the SQL Statement, when you should have a Table or Tables listed instead. Also, point to the Field Name within the Table (assuming Table1):

Code:

SQLrs = "SELECT * FROM Table1 WHERE Table1.[Time] < #" & time1 & "#;"

P.S. - I created a Code demo for you, based on the Northwind Sample Database, that adds a sequential number of Months (1 to 11) to a Base Date (1/1/1963), while creating Recordsets in which Birth Dates are > the derived Dates.

Code:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim intMonthCtr As Integer

strSQL = "SELECT * FROM Employees WHERE [BirthDate] > #"

Debug.Print "Counter", "Base Date", "Date", "Birth Date"

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("Employees", dbOpenForwardOnly)

For intMonthCtr = 1 To 11
 Set rst = MyDB.OpenRecordset(strSQL & DateAdd("m", intMonthCtr, #1/1/1963#) & _
                              "#;", dbOpenForwardOnly)
  With rst
    Do While Not .EOF
      Debug.Print intMonthCtr, "1/1/1963", DateAdd("m", intMonthCtr, #1/1/1963#), ![BirthDate]
        .MoveNext
    Loop
      Debug.Print "---------------------------------------------------------"
  End With
Next

rst.Close
Set rst = Nothing

Sample OUTPUT:

Code:

Counter       Base Date     Date          Birth Date
 1            1/1/1963      2/1/1963      12/8/1968 
 1            1/1/1963      2/1/1963      8/30/1963 
 1            1/1/1963      2/1/1963      7/2/1963 
 1            1/1/1963      2/1/1963      7/2/1969 
---------------------------------------------------------
 2            1/1/1963      3/1/1963      12/8/1968 
 2            1/1/1963      3/1/1963      8/30/1963 
 2            1/1/1963      3/1/1963      7/2/1963 
 2            1/1/1963      3/1/1963      7/2/1969 
---------------------------------------------------------
 3            1/1/1963      4/1/1963      12/8/1968 
 3            1/1/1963      4/1/1963      8/30/1963 
 3            1/1/1963      4/1/1963      7/2/1963 
 3            1/1/1963      4/1/1963      7/2/1969 
---------------------------------------------------------
 4            1/1/1963      5/1/1963      12/8/1968 
 4            1/1/1963      5/1/1963      8/30/1963 
 4            1/1/1963      5/1/1963      7/2/1963 
 4            1/1/1963      5/1/1963      7/2/1969 
---------------------------------------------------------
 5            1/1/1963      6/1/1963      12/8/1968 
 5            1/1/1963      6/1/1963      8/30/1963 
 5            1/1/1963      6/1/1963      7/2/1963 
 5            1/1/1963      6/1/1963      7/2/1969 
---------------------------------------------------------
 6            1/1/1963      7/1/1963      12/8/1968 
 6            1/1/1963      7/1/1963      8/30/1963 
 6            1/1/1963      7/1/1963      7/2/1963 
 6            1/1/1963      7/1/1963      7/2/1969 
---------------------------------------------------------
 7            1/1/1963      8/1/1963      12/8/1968 
 7            1/1/1963      8/1/1963      8/30/1963 
 7            1/1/1963      8/1/1963      7/2/1969 
---------------------------------------------------------
 8            1/1/1963      9/1/1963      12/8/1968 
 8            1/1/1963      9/1/1963      7/2/1969 
---------------------------------------------------------
 9            1/1/1963      10/1/1963     12/8/1968 
 9            1/1/1963      10/1/1963     7/2/1969 
---------------------------------------------------------
 10           1/1/1963      11/1/1963     12/8/1968 
 10           1/1/1963      11/1/1963     7/2/1969 
---------------------------------------------------------
 11           1/1/1963      12/1/1963     12/8/1968 
 11           1/1/1963      12/1/1963     7/2/1969

---------------------------------------------------------

**izharmel** · Aug 5 '10, 08:41 AM

Thank you
it works great

**izharmel** · Aug 5 '10, 10:56 AM

Debugging

Hi,
I thought I had the function working...
It actually works fine for some months but when I tried averaging July it doesn't take any data from rs to rsTemp.
It's as if the [Time] column doesn't fulfill the terms for the SELECT Query.

Code:

Function Av_Calc(AvNum As Integer, TableNme As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsTemp As DAO.Recordset
    Dim SQLrs As String
    Dim ShowField As String
    Dim FromTable As String
    Dim LastTime As Date
    Dim TopTime As Date
    Dim BottomTime As Date
    Dim RowsNum As Integer
    
    Set db = CurrentDb
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DROP TABLE T_Average" 'delete existing T_Average Table
    'create a new table with the same columns as T_Export
    DoCmd.TransferDatabase acExport, "Microsoft Access",_
 db.Name, acTable, "T_Export", "T_Average", True
    
    Set rs = db.OpenRecordset(TableNme, dbOpenSnapshot)
    rs.MoveLast
    LastTime = rs!Time  'save the time of the last row for the stopping condition
    rs.MoveFirst
    
    'stopping condition for a non round last loop
    Do While Not rs!Time > DateAdd("n", -AvNum, LastTime)  
        'add num of minutes requested to the current time
        TopTime = DateAdd("n", AvNum, rs!Time) 
        If TopTime > LastTime Then TopTime = LastTime
        BottomTime = rs!Time
        SQLrs = "SELECT * FROM " & TableNme & " WHERE " & TableNme & ".[Time] < #" & TopTime & "#"
        SQLrs = SQLrs & " AND " & TableNme & ".[Time] >= #" & BottomTime & "#"
        Set rsTemp = db.OpenRecordset(SQLrs, dbOpenDynaset) 'takes a segment of data to a new rs"
        If rsTemp.EOF Then Exit Function
        RowsNum = 0
        Do While rs!Time < TopTime
            RowsNum = RowsNum + 1
            rs.MoveNext
        Loop
        
        DoCmd.RunSQL "INSERT INTO T_Average([Time]) VALUES(#" & BottomTime & "#)"
        With rsTemp
            'Iterate all the Fields 'except' the 1st Field
            For intFldCtr = 1 To .Fields.Count - 1
                Do While Not .EOF
                    lngRunningTotal = lngRunningTotal + .Fields(intFldCtr).Value
                    .MoveNext
                Loop
                SQLrs = "UPDATE T_Average SET [" & rsTemp.Fields(intFldCtr).Name & "] = "
                SQLrs = SQLrs & "" & lngRunningTotal / RowsNum & " WHERE "
                SQLrs = SQLrs & "[Time]=#" & BottomTime & "#"
                DoCmd.RunSQL SQLrs
                lngRunningTotal = 0       'Must Reset after each Iteration
                .MoveFirst
            Next
        End With
        rsTemp.Close
        Set rsTemp = Nothing
    Loop

    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True
End Function

an example of an input table would be:
Time Data1 Data2
01/07/2010 04:28:00 293 1059
01/07/2010 04:28:20 185 1040
01/07/2010 04:28:40 57 375
01/07/2010 04:28:50 49 172
01/07/2010 04:29:00 32 85
01/07/2010 04:29:20 16 0
01/07/2010 04:29:40 14 0
01/07/2010 04:30:00 112 0
01/07/2010 04:30:20 129 0
01/07/2010 04:30:40 118 783
01/07/2010 04:31:00 89 189
01/07/2010 04:31:20 61 113
01/07/2010 04:31:30 273 118
01/07/2010 04:31:40 300 150
01/07/2010 04:32:00 364 908
01/07/2010 04:32:20 55 260
01/07/2010 04:32:30 27 59
01/07/2010 04:32:40 85 47
01/07/2010 04:32:50 39 49
01/07/2010 04:33:00 68 68
01/07/2010 04:33:20 81 82
01/07/2010 04:33:40 137 207
01/07/2010 04:33:50 125 422
01/07/2010 04:34:00 105 238
01/07/2010 04:34:10 134 194
01/07/2010 04:34:20 100 300
01/07/2010 04:34:40 101 250
01/07/2010 04:34:50 40 194
01/07/2010 04:35:00 23 59
01/07/2010 04:35:10 24 44

As I said, it works fine for some months but with this case it gives a blank table (because of the If rsTemp.EOF condition)

Can any one help?

Thanks in advance,

Izhar

**ADezii** · Aug 5 '10, 02:04 PM

Izhar, it makes no sense to me that thee Logic will work for some Months but not for July. If rstTemp.EOF evaluates to True, or more specifically if (rstTemp.BOF And rstTemp.EOF) evaluates to True, then there are simply no Records which meet the 10 Minute Range Criteria. Could this simply be the case?

**izharmel** · Aug 8 '10, 08:02 AM

I wish it were.
Since even when the function works, it's very slow (approx 10 minutes) I decided to try an approach it from an SQL point of view.
so far I have :
"SELECT [Time], AVG(other fields), INTO 'new Table' FROM 'old table' GROUP BY (time/60/AvNum)"
I think the average part is correct but displaying the time doesn't work well...

**izharmel** · Aug 8 '10, 11:58 AM

This is what I have so far

I'm kinda stuck...

Code:

Function Av_Calc(AvNum As Integer, TableNme As String)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim SQLrs As String
    Dim ShowField As String
    Dim Diff As Long
    
    Set db = CurrentDb
    DoCmd.SetWarnings False
    DoCmd.RunSQL "DROP TABLE T_Average" 'delete existing T_Average Table
    'create a new table with the same columns as T_Export
    DoCmd.TransferDatabase acExport, "Microsoft Access", db.Name, acTable, "T_Export", "T_Average", True
    
    Set rs = db.OpenRecordset(TableNme, dbOpenSnapshot)
    ' create a string containing the names of the fields in the table
    For i = 1 To rs.Fields.Count - 1
        ShowField = ShowField & ", AVG(" & rs.Fields(i).Name & ") "
        ShowField = ShowField & "AS 'AVG(" & rs.Fields(i).Name & ")'"
    Next
    
    Diff = DateDiff("s", "01/01/2010 00:00:00", rs![Time]) / 60 / AvNum
    SQLrs = "SELECT " & Diff & " AS 'Time'" & ShowField & ""
    SQLrs = SQLrs & " INTO T_Average FROM T_Export GROUP BY " & Diff & ""
    DoCmd.RunSQL SQLrs
    
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True
End Function

The function doesn't choose the AvNum segments properly.
So far it shows only one row, with the date part as a long number

**ADezii** · Aug 8 '10, 03:05 PM

I feel as though you are hitting a Dead End as far as the 10-Minute Segment creations go, thus a new approach:

Calculate the Overall Starting Date/Time
Calculate the Overall Ending Date/Time
Calculate the difference, in Minutes, between the Overall Start and End Date/Times
Calculate the Number of 10 Minute Segments in the difference quoted above
Loop thru all the 10 Minute Segments
Generate the appropriate SQL for each Interval
Loop as long as the End of a Calculated Segment is not > the Absolute Ending Date/Time
Figure out what you will do for the Overlapping Intervals

Demo Code below and Results achieved via Dummy Data

Code:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim dteStartTime As Date
Dim dteEndTime As Date
Dim intMinsDiff As Integer
Dim intNumOf10MinBlocks As Integer
Dim intCtr As Integer

Set MyDB = CurrentDb()
Set rst = MyDB.OpenRecordset("SELECT * FROM tblTimes ORDER BY [TheTime];", dbOpenSnapshot)

rst.MoveFirst: dteStartTime = rst![TheTime]
rst.MoveLast: dteEndTime = rst![TheTime]

intMinsDiff = DateDiff("n", dteStartTime, dteEndTime)
intNumOf10MinBlocks = intMinsDiff \ 10

For intCtr = 0 To intNumOf10MinBlocks
  If DateDiff("n", DateAdd("n", intCtr * 10, dteStartTime), dteEndTime) >= 10 Then
    Debug.Print "SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #" & DateAdd("n", intCtr * 10, dteStartTime) & _
                "# AND #" & DateAdd("n", 10, DateAdd("n", intCtr * 10, dteStartTime)) & "#;"
      'Average processing for each Segment here
  End If
Next

Code:

SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:00:00 AM# AND #8/8/2010 8:10:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:10:00 AM# AND #8/8/2010 8:20:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:20:00 AM# AND #8/8/2010 8:30:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:30:00 AM# AND #8/8/2010 8:40:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:40:00 AM# AND #8/8/2010 8:50:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:50:00 AM# AND #8/8/2010 9:00:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:00:00 AM# AND #8/8/2010 9:10:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:10:00 AM# AND #8/8/2010 9:20:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:20:00 AM# AND #8/8/2010 9:30:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:30:00 AM# AND #8/8/2010 9:40:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:40:00 AM# AND #8/8/2010 9:50:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:50:00 AM# AND #8/8/2010 10:00:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 10:00:00 AM# AND #8/8/2010 10:10:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 10:10:00 AM# AND #8/8/2010 10:20:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 10:20:00 AM# AND #8/8/2010 10:30:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 10:30:00 AM# AND #8/8/2010 10:40:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 10:40:00 AM# AND #8/8/2010 10:50:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 10:50:00 AM# AND #8/8/2010 11:00:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 11:00:00 AM# AND #8/8/2010 11:10:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 11:10:00 AM# AND #8/8/2010 11:20:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 11:20:00 AM# AND #8/8/2010 11:30:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 11:30:00 AM# AND #8/8/2010 11:40:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 11:40:00 AM# AND #8/8/2010 11:50:00 AM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 11:50:00 AM# AND #8/8/2010 12:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 12:00:00 PM# AND #8/8/2010 12:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 12:10:00 PM# AND #8/8/2010 12:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 12:20:00 PM# AND #8/8/2010 12:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 12:30:00 PM# AND #8/8/2010 12:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 12:40:00 PM# AND #8/8/2010 12:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 12:50:00 PM# AND #8/8/2010 1:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 1:00:00 PM# AND #8/8/2010 1:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 1:10:00 PM# AND #8/8/2010 1:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 1:20:00 PM# AND #8/8/2010 1:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 1:30:00 PM# AND #8/8/2010 1:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 1:40:00 PM# AND #8/8/2010 1:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 1:50:00 PM# AND #8/8/2010 2:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 2:00:00 PM# AND #8/8/2010 2:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 2:10:00 PM# AND #8/8/2010 2:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 2:20:00 PM# AND #8/8/2010 2:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 2:30:00 PM# AND #8/8/2010 2:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 2:40:00 PM# AND #8/8/2010 2:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 2:50:00 PM# AND #8/8/2010 3:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 3:00:00 PM# AND #8/8/2010 3:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 3:10:00 PM# AND #8/8/2010 3:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 3:20:00 PM# AND #8/8/2010 3:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 3:30:00 PM# AND #8/8/2010 3:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 3:40:00 PM# AND #8/8/2010 3:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 3:50:00 PM# AND #8/8/2010 4:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 4:00:00 PM# AND #8/8/2010 4:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 4:10:00 PM# AND #8/8/2010 4:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 4:20:00 PM# AND #8/8/2010 4:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 4:30:00 PM# AND #8/8/2010 4:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 4:40:00 PM# AND #8/8/2010 4:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 4:50:00 PM# AND #8/8/2010 5:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 5:00:00 PM# AND #8/8/2010 5:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 5:10:00 PM# AND #8/8/2010 5:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 5:20:00 PM# AND #8/8/2010 5:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 5:30:00 PM# AND #8/8/2010 5:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 5:40:00 PM# AND #8/8/2010 5:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 5:50:00 PM# AND #8/8/2010 6:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 6:00:00 PM# AND #8/8/2010 6:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 6:10:00 PM# AND #8/8/2010 6:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 6:20:00 PM# AND #8/8/2010 6:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 6:30:00 PM# AND #8/8/2010 6:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 6:40:00 PM# AND #8/8/2010 6:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 6:50:00 PM# AND #8/8/2010 7:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 7:00:00 PM# AND #8/8/2010 7:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 7:10:00 PM# AND #8/8/2010 7:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 7:20:00 PM# AND #8/8/2010 7:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 7:30:00 PM# AND #8/8/2010 7:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 7:40:00 PM# AND #8/8/2010 7:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 7:50:00 PM# AND #8/8/2010 8:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:00:00 PM# AND #8/8/2010 8:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:10:00 PM# AND #8/8/2010 8:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:20:00 PM# AND #8/8/2010 8:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:30:00 PM# AND #8/8/2010 8:40:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:40:00 PM# AND #8/8/2010 8:50:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 8:50:00 PM# AND #8/8/2010 9:00:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:00:00 PM# AND #8/8/2010 9:10:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:10:00 PM# AND #8/8/2010 9:20:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:20:00 PM# AND #8/8/2010 9:30:00 PM#;
SELECT * FROM tblTimes WHERE [TheTime] BETWEEN #8/8/2010 9:30:00 PM# AND #8/8/2010 9:40:00 PM#;

**izharmel** · Aug 9 '10, 05:30 AM

Thank you for the huge help.
I'll also try to change my approach and tell you how it goes.

**Delerna** · Aug 9 '10, 06:25 AM

SQL is the way to go if you want to improve the performance
Does this help?

Code:

INSERT INTO newTable
SELECT min([Time]),avg(otherfields)
FROM
(  SELECT year([Time]) as yr,
          month([Time]) as mth,
          day([Time]) as dy,
          hour([Time]) as hr,
          int(minute([Time])/[B]varMins[/B])*[B]varMins [/B]as mn,  
          [Time],
          {other fields)
    FROM [old table]
)a
GROUP BY yr,mth,dy,hr,mn

where varMins
is the entered number of minutes

How do I create an Average table

How do I create an Average table

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment