Results 1 to 11 of 11
  1. #1
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839

    Convoluted Counts

    All,
    I have been working at this for a bit and wanted to run this by for the CST. Common sense test, and assistance in putting this in VBA code. Where it doesn't bog down the system. like a DCount can. Maybe there is a need for arrays? To see with the figures referenced below, please look at the attached Word document, and the Excel spreadsheet.

    1. Starting at Filter where all RowType are MEQUIP (sorts out all the extra rows in the table)


    2. Filter where all ParentNodeID’s are the same (breaks down into groups of data), (Count Distinct ParentNodeID for the whole table)
    3. Where step 2 = 1 through DMAX(CountDistinct ParentNodeID), filter down to where all RoleID’s are the same (breaks down the Parent Node ID into sub-groups), (Count Distinct RoleID for the whole table)
    4. Where step 3 = 1 through DMAX(CountDistinctRoleID) , Find all ParentEquipID = platformID on the same row (This finds the base equipment subbed under the platform), Sort Ascending
    5. With the group of UniqueID’s found sort ascending where ParentEquipID = PlatformID , Determine the UniqueID ’s found = ParentEquipID,
    6. Count UniqueID where it is found in ParentEquipID in Step 5 to find where Count is > 1, Count and Store the UniqueID values by ascending order
    7. Count ParentEquipID in Step 7 to find where Count = 0, Store UniqueID values, Sort ascending UniqueID
    8. Count(UniqueID where ParentEquipID = UniqueID) CntUID=1
    9. Count CntUID + 1 through DMAX(CountDistinct UniqueID) of ParentEquipID in Step 8
    10. Count CntUID + 1 through DMAX(CountDistinct UniqueID) of ParentEquipID in Step 7
    11. Go to step 4, where count = 2 through DMAX(CountDistinctRoleID) , where ParentEquipID = platformID
    12. Go to step 3, where count = 2 through DmaX (Distinct RoleID)
    13. Go to step 2, where count = 2 through DmaX (Distinct ParentNodeID)


    Example:

    1. EXCEL spreadsheet
    Figure_1
    2. ParentNodeID (‘1222126‘) = 1
    ParentNodeID (‘1222129‘) = 2
    Figure_2
    3. RoleID (‘1222128’) = 1
    RoleID (‘1222131’) = 2
    RoleID (‘1222134’) = 3
    Figure_3
    4. ParentEquipID (‘3589359’) = 1
    ParentEquipID (‘3589363’) = 2
    ParentEquipID (‘3589364’) = 3
    ParentEquipID (‘3589365’) = 4
    Figure_4
    5. ParentEquipID (‘3589359’) = 1
    ParentEquipID (‘3589384’) = 2
    Figure_5
    6. ParentEquipID (‘3589384’) = UniqueID (‘3589384’)
    7. ParentEquipID <> UniqueID (‘3589375’)
    ParentEquipID <> UniqueID (‘3589385’)
    ParentEquipID <> UniqueID (‘3589386’)
    ParentEquipID <> UniqueID (‘3589393’)
    ParentEquipID <> UniqueID (‘3625368’)
    Figure_6
    8. UniqueID (‘3589384’) = 1, CntUID =1
    9. UniqueID (‘3589385’) = 1, CntUID = 1 + 1 = CntUID = 2
    UniqueID (‘3589386’) = 2, CntUID = 1 + 2 = CntUID = 3
    Figure_8
    10.ParentEquipID <> UniqueID (‘3589375’) = 1, CntUID = 3 + 1 = CntUID = 4
    ParentEquipID <> UniqueID (‘3589393’) = 2, CntUID = 3 + 2 = CntUID = 5
    ParentEquipID <> UniqueID (‘3625368’) = 3, CntUID = 3 + 3 = CntUID = 6
    12. Go to step 4, where count = 2 through DMAX(CountDistinctRoleID) , where ParentEquipID = PlatformID
    13. Go to step 3, where count = 2 through DmaX (Distinct RoleID)
    14. Go to step 2, where count = 2 through DmaX (Distinct ParentNodeID)
    Figure_14
    Attached Files Attached Files

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps in a simple sentence explain what you are trying to do rather how you are trying to do it. Many things are much easier to do than in excel and certainly you do not apply the same methodology. At first glance it looks like you would use a number of connected queries and aggregate

    For example the first line in your spreadsheet - what does the count of UniqueID represent? 4 of what?

  3. #3
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    Ajax,


    the count columns are the after affect of affect of running steps 2 through 13.
    i am trying to count all items of column RowType where it equal mequip. But with a lot of caveats. Any mequip who are parents of other mequip items get counted first, then the sub component next, repeat that process until there are only mequip without sub components, then count those until finished. Repeat that process until finished. Then start all over for the next set.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    think you must have included the wrong example - I see no rowtype or mequip columns and I don't see any sub components. I really don't want to get involved with 20 questions to try and understand your requirement. All I can suggest is it sounds like you will need a recursive vba function since the ACE rdbms (the free one provided with access) does not have a recursive capability - you would need to use sql server to do that.

  5. #5
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    I am trying to take this:
    Assigned To: Paragraph

    BCNIS
    BLISS-WSMR TDB
    C3 DRIVER
    JDCARS
    …..CLIENT
    …..NCAT TEMPLATE
    …..SCS
    …..SERVER
    MATREX
    …..FOM
    .....RTI
    NVToolSet
    …..3DVIZ
    …..CMS2
    …..SNAP SERVER
    OneSAF
    RICS2
    …..CTTK-DRA
    …..DPU
    …..IMS
    …..RPWS
    RTCA
    …..CDL
    …..HCI
    SIF
    SIM/STIM TBOC SERVER (C)
    SIMDIS
    STARSHIP
    TCRS
    TEST TALK

    to have a count in this group:

    26 BCNIS
    28 BLISS-WSMR TDB
    22 C3 DRIVER
    4 JDCARS
    8 CLIENT
    5 NCAT TEMPLATE
    7 SCS
    6 SERVER
    1 MATREX
    2 FOM
    3 RTI
    12
    NVToolSet
    14 3DVIZ
    13 CMS2
    15 SNAP SERVER
    30 OneSAF
    16 RICS2
    17 CTTK-DRA
    20 DPU
    19 IMS
    18 RPWS
    9 RTCA
    10 CDL
    11 HCI
    29 SIF
    21 SIM/STIM TBOC SERVER (C)
    27 SIMDIS
    24 STARSHIP
    23 TCRS
    25 TEST TALK


    Attachment has the data with the wanted outcome in green fill

    Attached Files Attached Files

  6. #6
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    The example I was referencing was included in the first post

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Sorry, I can't help. Your example may make sense to you, but not to me. How does BCNIS have a value of 28 when it has no child records. ditto the next two

  8. #8
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    If you look ar the examples, I want to count all the items sorted ascending on uniqueID that have children, Those children are counted by sorting ascending by UniquedID, then the next higher valued UniqueID with children is counted, ... until there are no more UniqueID's with children. Then all the remaining unitqueIDs, without children are counted by UniqueID ascending order.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I can see you have an ID and a unique_ID and a parent_equipment_item_id. What is the difference between ID and uniqueid?
    I can also see unique_ID occasionally appears in parent_equipment_item_id
    I don't see an ultimate parent (where the parent value would be null)
    I don't see any field which indicates level - implication therefore is this needs to be recursive, but without an ultimate parent, nowhere to start

    usual way to do this is to have a query which left joins the table to itself and again and again for the number of levels. Something like


    SELECT (myTable A LEFT JOIN myTable B ON A.uniqueID=B.parentID) LEFT JOIN myTable C ON B.UniqueID=C.ParentID etc

    then change to a group by query and do your counts

  10. #10
    Thompyt is offline Expert
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2014
    Location
    El Paso, TX
    Posts
    839
    The ID is used as a count in my dcounts Code, and it puts the Table back to sequence. I could substitute UniqueID for ID in the count. ID is useless in this instance.

    The parent of that has a different value in RowType it equals "PLAT" - the count on this would be 1 to how many "PLAT" are in the parentNodeID

    The 1st Level would be ParentNodeID (Like a Platoon)
    The 2nd Level would be Role/FE/NodeID as you can have several assigned to the ParentNodeID (A person in that Platoon)
    The 3rd Level would be platform_id as you can have several assigned to Role/FE/NodeID (Truck(s) assigned to that person in the Platoon)
    The 4th Level would be parent_equipment_item_id where it appears in unique_id (Piece(s) of equipment on a truck assigned to a person in that platoon);
    ............A. With children = parent_equipment_item_id <> platform_id
    ............B. Without children = parent_equipment_item_id = platform_id
    The 5th Level would be unique_id
    identified in the 4.A. Level (Sub-component of Piece(s) of equipment on a truck)
    The 6th Level would be unique_id identified in the 4.B. Level

    Note: All these would be sorted by unique_id at each level




    Base equipment Query:
    Code:
    SELECT Table1.unique_id, Table1.[Equip HB Name], Table1.parent_equipment_item_id, Table1.platform_id
    FROM Table1
    GROUP BY Table1.unique_id, Table1.[Equip HB Name], Table1.parent_equipment_item_id, Table1.platform_id, Table1.[Row Type]
    HAVING (((Table1.parent_equipment_item_id)=[platform_id]) AND ((Table1.[Row Type])="MEQUIP"))
    ORDER BY Table1.unique_id;
    Sub Component Query
    Code:
    SELECT Table1.unique_id, Table1.[Equip HB Name], Table1.parent_equipment_item_id
    FROM Table1
    WHERE (((Table1.parent_equipment_item_id)<>[platform_id]) AND ((Table1.[Row Type])="MEQUIP"))
    ORDER BY Table1.unique_id;
    ParentNodeID Query
    Code:
    SELECT DISTINCTROW Table1.[Parent Node ID], Count(Table1.[Parent Node ID]) AS [CountOfParent Node ID]
    FROM Table1
    GROUP BY Table1.[Parent Node ID]
    HAVING (((Table1.[Parent Node ID])<>""));
    ;


  11. #11
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure if you are asking a question but regret I will have to drop off now, early start tomorrow and at a client all day

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Numbers with sub counts
    By Thompyt in forum Access
    Replies: 8
    Last Post: 10-28-2014, 06:33 PM
  2. Why Are Counts Different?
    By johnywhy in forum Queries
    Replies: 9
    Last Post: 01-08-2014, 05:35 PM
  3. Query - Counts
    By maxx102 in forum Access
    Replies: 1
    Last Post: 12-14-2012, 12:49 PM
  4. too many counts!
    By Svear in forum Access
    Replies: 1
    Last Post: 03-07-2012, 10:14 PM
  5. Report which only counts
    By imintrouble in forum Access
    Replies: 1
    Last Post: 02-16-2012, 03:31 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