How do I create an Average table

[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

[ADezii]

@izharmel
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]

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]

@izharmel

1. 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.
2. Code Example:
   

   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

3. Sample Call to Function ignoring any Return Value:
   

   Call fCalcAveragesInTableValues("Table1")

4. Sample results based on Table1 containing 6 Fields, all LONG INTEGERS except the First Field:
   

   Field2 ==> 400
   Field3 ==> 8
   Field4 ==> 3000
   Field5 ==> 21
   Field6 ==> 33.3333333333333

[izharmel]

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

[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.

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]

@izharmel
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):

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.

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:

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]

Thank you
it works great

[izharmel]

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.

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]

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]

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]

I'm kinda stuck...

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]

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

1. Calculate the Overall Starting Date/Time
2. Calculate the Overall Ending Date/Time
3. Calculate the difference, in Minutes, between the Overall Start and End Date/Times
4. Calculate the Number of 10 Minute Segments in the difference quoted above
5. Loop thru all the 10 Minute Segments
6. Generate the appropriate SQL for each Interval
7. Loop as long as the End of a Calculated Segment is not > the Absolute Ending Date/Time
8. Figure out what you will do for the Overlapping Intervals
9. Demo Code below and Results achieved via Dummy Data
   

   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

   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]

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

[Delerna]

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

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])/varMins)*varMins as mn,  
          [Time],
          {other fields)
    FROM [old table]
)a
GROUP BY yr,mth,dy,hr,mn
 

where varMins
is the entered number of minutes

[izharmel]

hi,
I just fixed the problem (after days of work)

here's the 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
 
    SQLrs = "SELECT Min([Time]) "
    SQLrs = SQLrs & "AS 'Time'" & ShowField & " INTO T_Average FROM T_Export GROUP BY"
    SQLrs = SQLrs & " Int(DateDiff('s', '01/01/2010 00:00:00', [Time])/60/" & AvNum & ")"
    DoCmd.RunSQL SQLrs
 
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True
End Function
 

Thank you all very much for your help