creating a universal averaging function

[izharmel]

Hi,
I wrote (with some help) a function which receives a table of numerical data, and outputs another table which contains the average value from each user picked number of lines and the maximum from that range.

Function Average_Max(cntr As Integer)
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim time1 As String
    Dim av As Long
    Dim mx As Long
    Set db = CurrentDb
        
    DoCmd.SetWarnings False
' delete existing Average_Max Table
    DoCmd.RunSQL "DELETE T_Average_Max.* FROM T_Average_Max;"    
    SQLrs = "SELECT T_MAIN.Time, T_MAIN.Data FROM T_MAIN;"
    Set rs = db.OpenRecordset(SQLrs, dbOpenSnapshot)
    rs.MoveFirst
    rcnt = 0
    mx = rs!Data
    av = 0
    time1 = rs!Time
    Do While Not rs.EOF
        ' looping until reaching the counting limit
        If Not rcnt = cntr Then   
            av = av + rs!Data
            rcnt = rcnt + 1
            ' comparing against the max value
            If rs!Data > mx Then                  
        mx = rs!Data
        End If
        Else
            av = av / cntr
            DoCmd.RunSQL "INSERT INTO T_Average_Max(Time1
_, Average , Max) VALUES (#" & time1 & "#, " & av & ",
_ " & mx & ")"
            av = 0
            mx = 0
            rcnt = 0
            time1 = rs!Time
            rs.MovePrevious
        End If
        rs.MoveNext
    Loop
    DoCmd.SetWarnings True
    rs.Close
    db.Close
    Set rs = Nothing
    Set db = Nothing
End Function

What I'm trying to do is make the function more universal.
For instance, if I were to give it an unknown table, with more than two columns, I want it to output a table with averages and max values for all the columns in the table.

Thanks in advance,

Izhar

[Killer42]

As you haven't received a reply to this in four weeks, I'd suggest (if you still need help with it) that you try posting it again in the "Microsoft Access / VBA" forum.

Hopefully you'll get a quicker response there.