Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 35
  1. #16
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Steve,
    Not quite, Below is what I'm looking for:

    BaseCNT unique_id parent_equipment_item_id
    0 3531966 3531961
    0 3531968 3531961
    0 3531969 3531961
    0 3531970 3531961
    0 3531974 3531961
    0 3531980 3531961
    0 3531962 3531961
    0 3531964 3531961
    0 3531971 3531961
    0 3531978 3531961
    0 3531977 3531961
    0 3531986 3531961
    0 3531975 3531961
    0 3531983 3531961
    0 3531979 3531961
    0 3531982 3531961
    1 3531963 3531962
    2 3531965 3531964
    3 3531972 3531971
    3 3531973 3531971

    I'm looking to count only the unique_id's where they have a parent different from the platform_id



    104(V)6 - 3531962 - is parent of 150(C) - 3531963
    114(V)3 - 3531964 - is parent of 117G(V)4 - 3531965
    92F(C) - 3531971 - is parent of RT-1523(C)/U - 3531972 and 3531973

    If you look at tblCountMEquip ID 9,10,11 in WChildCnt Column They all = 1 I need ID 9 = 1, ID 10 = 2, ID 11 = 3.
    Also BaseCnt would reflect in ID 6 = 2, ID 7 = 1, and ID 17 and 18 = 3

    See attached:



    Attached Files Attached Files

  2. #17
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Steve,
    Thanks for the links. I took a quick look ar them in the morning, but work hasn't allowed me anytime to study. I think the example you provided was a ranking.

  3. #18
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Uhhh, you're welcome.
    But Vlad (Gicu) posted the links in Post #14 and the dB in Post # 15.

  4. #19
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    I'm sorry, but this is totally new from the initial request and keeps changing; there was no mentioning of the platform id until post #13 and the requirements might be clear for you but are definitively not for me/us. You will need to include a better example (there is no platform ID in your latest pic), better sample data (the previous file had only one platform id). Please use Excel or any other tool to create your desired output showing at least the three ID columns involved (platform/parent/unique) sorted in your desired order and with the desired ranks (or sort orders).

    Cheers,
    Vlad

  5. #20
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Apologies for mixing Steve and Vlad up. Vlad has been the one assisting. Vlad, sorry for the confusion. The last one I pasted was the table you made before I posted. Test1-3MAR19 - Vlad. With Query named qryEquip_BaseCNT. I am still referencing the same single platform_id as depicted in Test1-4MAR19 - Wayne. I added some queries into it to demonstrate what I am doing. Attached is an excel with the desired outcome.
    Attached Files Attached Files

  6. #21
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Apologies for mixing Steve and Vlad up. Vlad has been the one assisting. Vlad, sorry for the confusion. The last one I pasted was the table you made before I posted. Test1-3MAR19 - Vlad. With Query named qryEquip_BaseCNT. I am still referencing the same single platform_id as depicted in Test1-4MAR19 - Wayne. I added some queries into it to demonstrate what I am doing. Attached is an excel with the desired outcome.


  7. #22
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was also assisting (or trying to), but you and Vlad are getting closer to a solution, so I'll back out.......



    Good luck with your project.

  8. #23
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Wayne,

    When I think I'm making progress with understanding your logic you throw another curve ball and all comes crushing down...
    Click image for larger version. 

Name:	DesiredOutcome.png 
Views:	29 
Size:	240.3 KB 
ID:	37709

    And I think you are trying to sort or rank the list not actually count. What are the rules for sorting? I am including the tblCountMEquip generated by your queries, maybe you can use that to illustrate the desired outcome and the associated logic/sub-components. I think it should be fairly easy to achieve once laid down in a clear way.

    Cheers,
    Vlad
    Attached Files Attached Files

  9. #24
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    Agree I am sorting to make a count for a later step involving moving all this vertical data sets to horizontal when assigned to the platform. I'm setting it up for Visio. (This is Step .001 for a later project)

    I Could make a count of all the unique_ids as ascending, but that places equipment in the wrong spot/sequence. if you notice in your excel above, they are all assigned to 1 platform, but not in accordance with the ID. The original database this cam from is susceptible to the way the data was input originally.


    I am sorting to make all unique_ids where that unique_id shows up in the parent_equip…..id column first. (104(V)6)
    Then putting the child equipment right after the parent. (150(C))
    Then the next parent, the child/children such as [DesiredOutcome] counts of 5,6,7.

    After all the parent/child are exhausted, the unique_id/without children are next. Position in this part of the sort at this point doesn't matter, Access auto sorts unique_id as ascending.

    Nz(DMax("[WChildCnt]","[tblCountMEquip]","[platform_id]='" & [platform_id] & "'"),0)+0 get highest count of the last Child. Adding this causes the unique_id without child to follow after the unique-id with children.


    BLUF - unique_id with child(ren) set first, Next unique_id without children

    unique_id with child(ren)
    …..child(ren)
    unique_id with child(ren)
    …..child(ren)
    unique_id with child(ren)
    …..child(ren)
    …………………………………….until exhausted

    unique_id with out children

    Next platform_id

    Rinse - repeat.

    If this was MS.Word then the child(ren) would show as indented from the parent.

    92F
    …..RT-1523
    …..RT-1523


  10. #25
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Much better now!

    Here you go, please review and let me know if that is what you wanted.

    Cheers,
    Vlad
    Attached Files Attached Files

  11. #26
    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,

    Have a had a chance to look at the qryFinalSort?

    Cheers,
    Vlad

  12. #27
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    Sorry I haven't answered yet, I have been plugging away at work overload and tweaking the code to what I am trying to do. It works like a charm with the parent & Parent with Child. I have yet to figure out to make the count for Parent with out child.
    qryFinalSort worked beautifully for the single platform_id and now I am trying to put it within the parent with child loopset to count dmax+1 on that platform_id, then rstID.movenext to the next platform. I should get a count of less than 40. I really appreciate your help.

    Thanks!
    Wayne

  13. #28
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Vlad,
    I thought I had the parent with child right. I canged the latd icounter = icounter +1 to Icounter = 1 it gets the specific counts per parent with child but does not keep the count for that platform_id.

    Code:
    Do Until rstId.EOF
    'first the parent
        sUniqueID =rstId("unique_ID")
        rstId.Edit
       rstId("EndCnt") = iCounter
        rstId.Update
    'now the child/children
            sSQLChild ="SELECT Table1.* FROM Table1 WHERE Table1.parent_equipment_item_id ='" & sUniqueID & "' ORDER BY Table1.ID;"
            SetrstChild = CurrentDb.OpenRecordset(sSQLChild, dbOpenDynaset)
                DoUntil rstChild.EOF
                   iCounter = iCounter + 1
                   rstChild.Edit
                   rstChild("EndCnt") = iCounter
                   rstChild.Update
                   rstChild.MoveNext
                Loop
               iCounter = 1
        rstId.MoveNext
    Loop

  14. #29
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Wayne, I am on my phone right now, would you please upload a sample db with your code and updated data foe multiple platforms?
    Vlad

  15. #30
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Not sure what you want to achieve there, you need to increment the counter before moving to the next record.

    Cheers,
    Vlad

Page 2 of 3 FirstFirst 123 LastLast
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