Update a groupBy query in code

[jmarcrum]

Hey everyone,

I have a question that's been troubling me for a bit. I work for Alabama Power. I have 1 table (tblOutages). Every morning I import a list of Power Outages for the state of Alabama into that table. I have a yes/no (chkOutage) field set-up in the table to set chkOutage = TRUE on every record that is imported.

Here's my question, and it's a bit long. :) I would be much appreciative if anyone could help!

At the end of the import I am trying to run a query that pulls all of the records that have the chkOutage field set to TRUE. In code, I have that set-up as sqlStatement1. That part is done. Now (for the difficult part)....in the table, each outage has a CASE NUMBER field and a REFERENCE NUMBER field. The REFERENCE NUMBER is unique to the outage (no other outage has the same reference number), but the CASE NUMBER is not unique (other outages can be assigned to the same case number via location within the state). For example, a certain CASE NUMBER might have multiple REFERENCE NUMBERS (kind of like an ID for the record)...

:)

So...having said that...Every outage also has a CMI (CUSTOMER MINUTES INTERRUPTED - basically, it holds how many minutes that the certain REFERENCE NUMBER was without power). I want to run a GROUPBY query that pulls all REFERENCE NUMBERS in the table, maybe as a 'COUNT' and GROUP them by CASE NUMBER, but they MUST have the chkOutage field set to TRUE AND the CMI 'SUM' must be < 25,000. I want to make this sqlStatement1.

Then for EVERY INDIVIDUAL RECORD that's accounted for in the GROUPBY query, I want to run an UPDATE query on other certain fields that go along with those individual records.

This has got me banging my head against the wall for a couple days now.

Here's my function that I run at the end of the import, currently it says that I am missing 1 parameter...

Public Function CheckOutagesForReview()
 
Dim sqlStatement1 As String
Dim sqlStatement2 As String
Dim dbs_curr As Database
Dim records1 As Recordset
Dim records2 As Recordset
 
Set dbs_curr = CurrentDb
sqlStatement1 = "SELECT tblOutages.CaseNumber, Count(tblOutages.RefNumber) AS CountOfRefNumber, Sum(tblOutages.CMI) AS SumOfCMI, tblOutages.chkOutages FROM tblOutages GROUP BY tblOutages.CaseNumber, tblOutages.chkOutages HAVING (((tblOutages.chkOutages)=True));"
Set records1 = dbs_curr.OpenRecordset(sqlStatement1, dbOpenDynaset, dbSeeChanges, dbOptimistic)
 
sqlStatement2 = "SELECT * FROM tblOutages WHERE (((tblOutages.CaseNumber) In (SELECT CaseNumber FROM qryCheckOutages WHERE sqlStatement1.CaseNumber=tblOutages.CaseNumber)));"
Set records2 = dbs_curr.OpenRecordset(sqlStatement2, dbOpenDynaset, dbSeeChanges, dbOptimistic)
 
'CMI Check
With records1
    If records1!SumOfCMI < 25000 Then
        While Not records2.EOF
            records2.Edit
            records2!Printed = True
            records2!Status = 4
            records2!Findings = "Outage did not meet minimum requirements for review"
            records2.Update
        Wend
    End If
End With
 
'Tree Growth and Substation lock out check
While Not records2.EOF
    With records2
        If records2!Cause = 38 Or (records2!Switch = records2!FeederNumber) Then
            records2.Edit
            !Printed = False
            !Status = 1
            records2.Update
        Else
            records2.Edit
            !Printed = True
            !Status = 4
            !Findings = "Outage did not meet minimum requirements for review"
            records2.Update
        End If
    End With
Wend
 
End Function  

[gpl]

It sounds very complicated, I would approach this by trying to simplify the problem.

Firstly, when you have identified the groups that need updating, load the keys into an intermediate table. You should then be able to run your update with reference to these keys.

Does that help ?
Graham