By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,427 Members | 1,102 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,427 IT Pros & Developers. It's quick & easy.

Update a groupBy query in code

P: 105
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) 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...

Expand|Select|Wrap|Line Numbers
  1. Public Function CheckOutagesForReview()
  3. Dim sqlStatement1 As String
  4. Dim sqlStatement2 As String
  5. Dim dbs_curr As Database
  6. Dim records1 As Recordset
  7. Dim records2 As Recordset
  9. Set dbs_curr = CurrentDb
  10. 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));"
  11. Set records1 = dbs_curr.OpenRecordset(sqlStatement1, dbOpenDynaset, dbSeeChanges, dbOptimistic)
  13. sqlStatement2 = "SELECT * FROM tblOutages WHERE (((tblOutages.CaseNumber) In (SELECT CaseNumber FROM qryCheckOutages WHERE sqlStatement1.CaseNumber=tblOutages.CaseNumber)));"
  14. Set records2 = dbs_curr.OpenRecordset(sqlStatement2, dbOpenDynaset, dbSeeChanges, dbOptimistic)
  16. 'CMI Check
  17. With records1
  18.     If records1!SumOfCMI < 25000 Then
  19.         While Not records2.EOF
  20.             records2.Edit
  21.             records2!Printed = True
  22.             records2!Status = 4
  23.             records2!Findings = "Outage did not meet minimum requirements for review"
  24.             records2.Update
  25.         Wend
  26.     End If
  27. End With
  29. 'Tree Growth and Substation lock out check
  30. While Not records2.EOF
  31.     With records2
  32.         If records2!Cause = 38 Or (records2!Switch = records2!FeederNumber) Then
  33.             records2.Edit
  34.             !Printed = False
  35.             !Status = 1
  36.             records2.Update
  37.         Else
  38.             records2.Edit
  39.             !Printed = True
  40.             !Status = 4
  41.             !Findings = "Outage did not meet minimum requirements for review"
  42.             records2.Update
  43.         End If
  44.     End With
  45. Wend
  47. End Function  
Apr 16 '08 #1
Share this Question
Share on Google+
1 Reply

P: 152
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 ?
Apr 17 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.