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...
[CODE=VB]Public Function CheckOutagesFor Review()
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.Case Number, Count(tblOutage s.RefNumber) AS CountOfRefNumbe r, Sum(tblOutages. CMI) AS SumOfCMI, tblOutages.chkO utages FROM tblOutages GROUP BY tblOutages.Case Number, tblOutages.chkO utages HAVING (((tblOutages.c hkOutages)=True ));"
Set records1 = dbs_curr.OpenRe cordset(sqlStat ement1, dbOpenDynaset, dbSeeChanges, dbOptimistic)
sqlStatement2 = "SELECT * FROM tblOutages WHERE (((tblOutages.C aseNumber) In (SELECT CaseNumber FROM qryCheckOutages WHERE sqlStatement1.C aseNumber=tblOu tages.CaseNumbe r)));"
Set records2 = dbs_curr.OpenRe cordset(sqlStat ement2, dbOpenDynaset, dbSeeChanges, dbOptimistic)
'CMI Check
With records1
If records1!SumOfC MI < 25000 Then
While Not records2.EOF
records2.Edit
records2!Printe d = True
records2!Status = 4
records2!Findin gs = "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!Switc h = records2!Feeder Number) 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 [/CODE]
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...
[CODE=VB]Public Function CheckOutagesFor Review()
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.Case Number, Count(tblOutage s.RefNumber) AS CountOfRefNumbe r, Sum(tblOutages. CMI) AS SumOfCMI, tblOutages.chkO utages FROM tblOutages GROUP BY tblOutages.Case Number, tblOutages.chkO utages HAVING (((tblOutages.c hkOutages)=True ));"
Set records1 = dbs_curr.OpenRe cordset(sqlStat ement1, dbOpenDynaset, dbSeeChanges, dbOptimistic)
sqlStatement2 = "SELECT * FROM tblOutages WHERE (((tblOutages.C aseNumber) In (SELECT CaseNumber FROM qryCheckOutages WHERE sqlStatement1.C aseNumber=tblOu tages.CaseNumbe r)));"
Set records2 = dbs_curr.OpenRe cordset(sqlStat ement2, dbOpenDynaset, dbSeeChanges, dbOptimistic)
'CMI Check
With records1
If records1!SumOfC MI < 25000 Then
While Not records2.EOF
records2.Edit
records2!Printe d = True
records2!Status = 4
records2!Findin gs = "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!Switc h = records2!Feeder Number) 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 [/CODE]
Comment