Page 3 of 3 FirstFirst 123
Results 31 to 35 of 35
  1. #31
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    The attached shows the desired outcome.
    Ignore this one, I attached the incorrect zip. It'll have to wait until tomorrow.
    Attached Files Attached Files
    Last edited by Thompyt; 03-07-2019 at 09:09 PM.

  2. #32
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    I finally got time to play with the db. I have it for where there is only 1 parent on a platform, but if there is 2 or more the count goes out of whack. Attached is the desired outcome in excel, and the db. The excel is reflected in a table in the db.

    I am trying to get:
    Platform1
    …..Parent1 Count = 1
    ……….Child1
    Count = 2
    ……….Child2
    Count = 3
    …..Parent2
    Count = 4

    ……….Child1
    Count = 5

    ……….Child2
    Count = 6....

    Reset

    Platform2
    …..Parent1 Count = 1
    ……….Child1 Count = 2
    ……….Child2
    Count = 3
    …..Parent2
    Count = 4

    ……….Child1
    Count = 5

    ……….Child2
    Count = 6....
    Attached Files Attached Files

  3. #33
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Hi Wayne,
    Sorry I am away in vacation for a couple of weeks, back in early April.
    If I recall correctly in the sample data I used for my original code there was a parent with 2 children and the count seemed OK. I would suggest you try to break the logic into smaller groups and loop within those before moving to the next one.
    Let me know if you still need help around April 3 or so.
    Cheers,
    Vlad

  4. #34
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    figured it out. Thanks

    Code:
    Private Sub Counter_Click()
    Dim rstId As DAO.Recordset, rstChild As DAO.Recordset,rstPar As DAO.Recordset
    Dim sUniqueID As String, sPlatformID As String
    Dim sSQLPar As String, sSQLChild As String, sSQL AsString
    Dim iCounter As Integer
    
    ' Reset EndCnt to 0
    sSQL = "UPDATE Table1 SET Table1.EndCnt='0';"
    CurrentDb.Execute sSQL, dbFailOnError
    
    ' Start sorting
    sSQL = "SELECT qryMEQUIPWithChild.* FROMqryMEQUIPWithChild Where [Row Type]='MEQUIP' and Expr1 <> 0;"
    Set rstId = CurrentDb.OpenRecordset(sSQL, dbOpenDynaset)
    
    iCounter = 1
    
    ' Parents with Children
    Do Until rstId.EOF
    ' Parents
        sPlatformID =rstId("platform_id")
        sUniqueID =rstId("unique_id")
        rstId.Edit
       rstId("EndCnt") = iCounter
        rstId.Update
    ' Children
        sSQLChild ="SELECT Table1.* FROM Table1 WHERE Table1.parent_equipment_item_id ='" & sUniqueID & "' AND[Row Type]='MEQUIP' ORDER BYTable1.ID;"
        Set rstChild =CurrentDb.OpenRecordset(sSQLChild, dbOpenDynaset)
            Do UntilrstChild.EOF
            iCounter =iCounter + 1
               rstChild.Edit
               rstChild("EndCnt") = iCounter
               rstChild.Update
               rstChild.MoveNext
            Loop
           rstId.MoveNext
        If rstId.EOF =True Then
            Exit Do
        End If
        If sPlatformID= rstId![platform_id] Then
            iCounter =iCounter + 1
        ElseIfsPlatformID <> rstId![platform_id] Then
            iCounter =1
        End If
    Loop
    
    ' Childless
            sSQLPar ="SELECT qryMEQUIPWithChild.* FROM qryMEQUIPWithChild WHERE [Row Type]='MEQUIP'and qryMEQUIPWithChild.EndCnt = 0 ORDER BY qryMEQUIPWithChild.ID;"
            Set rstPar= CurrentDb.OpenRecordset(sSQLPar, dbOpenDynaset)
            Do UntilrstPar.EOF
               sPlatformID = rstPar![platform_id]
        If sPlatformID= rstPar![platform_id] Then
                   iCounter = rstPar![Expr2] + 1
        ElseIfsPlatformID <> rstPar![platform_id] Then
            iCounter =0
        End If
               rstPar.Edit
               rstPar![EndCnt] = iCounter
               rstPar.Update
               rstPar.MoveNext
            Loop
    End Sub

  5. #35
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear!
    Take care,
    Vlad

Page 3 of 3 FirstFirst 123
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Shortening if / dlookup >0 / then dlookup
    By Jack Russel in forum Modules
    Replies: 1
    Last Post: 02-19-2016, 03:01 PM
  2. DLookup
    By Alex Motilal in forum Programming
    Replies: 2
    Last Post: 04-20-2015, 11:05 PM
  3. DLookup help
    By mike02 in forum Queries
    Replies: 1
    Last Post: 08-07-2013, 02:29 PM
  4. Need help with a DLookup using Or
    By jax1000rr in forum Queries
    Replies: 1
    Last Post: 05-06-2013, 11:18 AM
  5. DLookUp Help
    By ThaGreenMoose in forum Forms
    Replies: 10
    Last Post: 06-15-2011, 12:08 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums