Loop within a loop for min and max

[dj59]

I have a query that has a column with “true” or “false” in it.

I have vba code that loops through all the rows and finds the first “true” value in a column and saves certain info to a table based on that first row of the record set; it continues to loop until it finds the first “false” value and saves info from that row to the same table.

Now, I need to take the first record set of “true” values and loop through that record set and find the minimum and maximum value from a field in that record set, then update the table with those 2 amounts; then continue looping until the next record set of “true” values.

Here is the code I’m using to loop. This works well.

Private Sub CmdAnalyzeFridge_Click()
On Error GoTo Err_cmdTest_Click
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim rstResults As DAO.Recordset
Dim intFldCtr As Integer
Dim blnFoundATrue As Boolean
Dim varEventID As Variant
Dim varPIN As Variant
Dim varColumnName As Variant
Dim varStartDate As Variant
Dim varStartTime As Variant
Dim varEndDate As Variant
Dim varEndTime As Variant
Dim intResponse As Integer
Dim varStartDateTime As Variant
'Dim varMin As Variant
'Dim varMax As Variant
blnFoundATrue = False
'DELETE all previous Results from the Results Table
CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError
Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("qryFridge", dbOpenSnapshot)
Set rstResults = MyDB.OpenRecordset("tblResults", dbOpenDynaset)
DoCmd.Hourglass True        'Indicate activity, at least
With rst
  For intFldCtr = 7 To .Fields.Count - 1        'Skip all Fields except 5 (flagged as true or false)
    Do While Not .EOF
      If .Fields(intFldCtr) = True Then
        If Not blnFoundATrue Then        '1st true Value not found, but Field is True
          varEventID = .Fields(0)
          varPIN = .Fields(2)
          varStartDateTime = .Fields(1)
          varColumnName = .Fields(intFldCtr).Name
          varStartDate = Format(.Fields(1), "mm/dd/yyyy")
          varStartTime = Format(.Fields(1), "hh:mm AM/PM")
 
            'call Min and Max Module
            mdl_MinMax.MinOfList
          varMin = .Fields(3)
            mdl_MinMax.MaxOfList
          varMax = .Fields(3)
 
            blnFoundATrue = True         'Found 1st True, Reset
 
        End If       '1st True was found, now need the 1st False
      Else      'Field is False
        If blnFoundATrue Then     'False Value with a True found
          varEndDate = Format(.Fields(1), "mm/dd/yyyy")
          varEndTime = Format(.Fields(1), "hh:mm AM/PM")
 
            With rstResults
              .AddNew
                 ![EventID] = varEventID
                 ![PIN] = varPIN
                 ![Column] = varColumnName
                 ![Start Date] = varStartDate
                 ![Start Time] = varStartTime
                 ![End Date] = varEndDate
                 ![End Time] = varEndTime
                 ![Diff (mins)] = DateDiff("n", varStartDateTime, rst.Fields(1))
                 ![Diff2] = fCalcTime(DateDiff("n", varStartDateTime, rst.Fields(1)))
                 ![MinTemp] = varMin
                 ![MaxTemp] = varMax
              .Update
            End With
            Debug.Print varMin & " " & varMax
            'Debug.Print varEventID & " " & varColumnName & " " & " " & varStartDate & " " & _
                         varStartTime & " " & varEndDate & " " & varEndTime & " " & _
                         DateDiff("n", varStartTime, varEndTime) & " minutes"
            blnFoundATrue = False         'RESET for next True Run - same Column
        End If
      End If
        .MoveNext
    Loop
      'RESET Variables in preparation for next Column move
      varEventID = Null
      varPIN = Null
      varColumnName = Null
      varStartDate = Null
      varStartTime = Null
      varEndDate = Null
      varEndTime = Null
      varStartDateTime = Null
      varMin = Null
      varMax = Null
        blnFoundATrue = False
      .MoveFirst
  Next
End With
 
DoCmd.Hourglass False
rstResults.Close
rst.Close
Set rst = Nothing
Set rstResults = Nothing
intResponse = MsgBox("View Results Table?", vbInformation + vbYesNo + vbDefaultButton1, "Results Table")
If intResponse = vbYes Then
  DoCmd.OpenQuery "qryResults", acViewNormal, acReadOnly
  DoCmd.Maximize
End If
Exit_CmdAnalyzeFridge_Click:
  Exit Sub
Err_CmdAnalyzeFridge_Click:
  DoCmd.Hourglass False
    MsgBox Err.Description, vbExclamation, "Error in CmdAnalyzeFridge_Click()"
    Resume Exit_CmdAnalyzeFridge_Click
End Sub

I have a function in a module to find min or max code, but it looks at the entire file and gives a min and max of the entire file, not just each record set.

The function code I'm using is from here:
http://allenbrowne.com/func-09.html

Can you tell me how to call the min-max function into the loop for each recordset, rather than the whole file?
Thank you.

 

[MarkK]

You don't need loops to find Min and Max, you can just use SQL, but maybe describe what your overall purpose is, since I'm betting there are optimizations that can be introduced upstream from where you've started describing "the problem." Like, it looks like you might not have normalized tables if you are programmatically selecting a field in a loop, then changing the field, and running the same loop again. That is an uncommonly laborious thing to do in code, and is typically symptomatic of a structural problem with your table design.
hth

 

[dj59]

Yes, possibly I should do the min max as a separate sql and append to my table, but I'm stuck on how to procede with that too....

Attachment1 is a snapshot named "loop1" that reflects the query that the vba code loops through to find the first "true" row

Attachment2 is a snapshot named "loop2" that reflects the results table.
The last two rows of Attachment2 is where the min and max amounts should go from the "true" record sets. I manually entered in what should go in those columns based on the query

These attachments are badly named:
loop1 = query
loop2 = results

This is difficult to explain, so hopefully the attachments will come through and you can see what I'm trying to do.

Thanks.

 

[MarkK]

To find the record with the least value in Field "A" for criteria "B", write a query that only returns records that satisfy criteria "B", sort ascending on field "A", and take the first record. Does that make sense?

But here's the thing, if your raw data is structured correctly, you don't commonly need to move it around in coded loops. Results can be generated not by running loops in recordsets, but by writing queries that draw summarized data from related tables, and SQL functions to do this include Sum(), Count(), Min(), Max(), First(), Last(), Avg() which are very powerful and very fast.

How is your raw data structured that you can't just query that directly? I'm betting that if you spend a little time normalizing your upstream source data, you'll end up saving yourself tons of time on this kind of downstream processing, which seems excessively laborious.

 

[dj59]

Thank you for your time.
I don't believe I am explaining my issue clearly.
I know how to use sql as you have stated, but in this case, it isn't as simple as that.
I will, however look at reworking the entire thing and find a better way to do this.