microsoft.public.excel.programming
[Top] [All Lists]

Looking for faster way to find minimum value

Subject: Looking for faster way to find minimum value
From:
Date: 28 Jun 2006 13:05:50 -0700
Newsgroups: microsoft.public.excel.programming
I have an application that stores values on 15 minute intervals. In one
function, I need to find the minimum average value over a 3 hour
period. So I make a query for each 3 hour period, compare it to the
last period, and save it if it has a lower value. The time is
tolerable, but pretty annoying (about 25 seconds). I want to know if
anyone has any suggestions for speeding up this process. My code is
below. Thanks!!


    Do
        EndAM = StartAM + #3:00:00 AM#

        'Query with records during this 3 hour period
        strQry = "SELECT INFLOW.FM_FLOW FROM inflow WHERE
(((Int([INFLOW]![FM_DATE]))=#" + Format(dtForm, "mm-dd-yy") + "#) AND
((INFLOW.FM_FMID)=" + strFMID + ") AND
((([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))>=#" + Format(StartAM,
"ttttt") + "# And (([INFLOW]![FM_DATE]-Int([INFLOW]![FM_DATE])))<#" +
Format(EndAM, "ttttt") + "#));"
        If fQueryExist(qryName) Then db.QueryDefs.Delete qryName

        Set qdfAM = db.CreateQueryDef(qryName, strQry)

        'Find average value for this 3 hour period
        tmpAvgAM = DAvg("fm_FLOW", qryName)
        If AvgAM > tmpAvgAM Then
            AvgAM = tmpAvgAM
        End If
        tmpAvgAM = 0
        StartAM = StartAM + IntervalAM
        db.QueryDefs.Delete qdfAM.Name
        Set qdfAM = Nothing
        
    Loop While EndAM < LastAM


<Prev in Thread] Current Thread [Next in Thread>