Average time

[izharmel]

Hi,
I have a function in Access VBA which uses an SQL code to create an average table from an existing table.
The original table may look something like this:

Time Data_1
03/08/2010 12:41:20 10
03/08/2010 12:41:30 13
03/08/2010 12:41:40 15
03/08/2010 12:41:50 23
.
.
.

with many thousands of rows.
I'm trying to create a function that outputs a new table containing the average values of data_1 (in segments the user chose) - for 10 minutes:

Time AVG(Data_1)
03/08/2010 12:41:20 10
03/08/2010 12:51:20 23
03/08/2010 13:01:20 13
03/08/2010 13:11:20 25
.
.
.

so far I have:

SQLrs = "SELECT [Time], AVG(Data_1) AS 'AVG(Data_1)'"
SQLrs = SQLrs & " INTO T_Average FROM T_Export"
SQLrs = SQLrs & " GROUP BY (time_in_seconds/" & AvNum & "/60)"

 DoCmd.RunSQL SQLrs

the function is supposed to average the values in data_1 and group them by the time (converted into seconds) divided by the user picked number of minutes (the 60 is for seconds)

Can any one help?

Thanks in advance,

Izhar

[izharmel]

The actual function is a bit more complicated:

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

I'm stuck with the SQL part (which I'm quite new to).

What I want to do is create a new table (T_Average) containing average values of all the fields from the original table (T_Export), in segments which appear in AvNum in minutes.
If the user chooses AvNum=3, then I want to GROUP each 3 minutes worth of data and output a row containing the time of the beginning of the segment and the average values of all the fields in the table during that time.

Thanks in advance,
Izhar

[izharmel]

Averaging function

fixed the problem!

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

[NeoPa]

It hardly seems worth answering now, but in case the concept is of interest (rather than some specific code) then the GROUP BY clause is the issue. How to group records together in batches of X minutes?

The trick is to divide (integer divide to lose any fractional part) the date/time value by X (working in minutes or X * 60 working in seconds).

Moving on. I noticed this languishing in the SQL Server forum. I expect this is related to your newness dealing with SQL. SQL =/= SQL Server. Your question is related to MS Access and Jet-SQL. I will move it across for you.

BTW. This probably explains the lack of interest in your question. You were asking a set of experts who largely have little to do with the subject of your question.