Results 1 to 8 of 8
  1. #1
    Bgum is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Posts
    5

    2nd Derative Aggregation Query

    This is a pretty difficult problem and Im at a lost for how to approach.
    Could you help me learn how to approach this problem correct?
    ------------
    High Level:
    The database tracks motor loads. Via relationships, users are able to assign motors to electrical panels.

    Example:
    Motor-A and Motor B are supplied with electrical power from electrical Panel-1
    Motor-A has a load of 100Watts
    Motor-B has a load of 50Watts
    Using a aggregation query, I can "group-by" electrical panel and calculate total load for each panel.

    Now I know the total motor load on each electrical panel
    Click image for larger version. 

Name:	1st Summation Query Results.png 
Views:	26 
Size:	13.8 KB 
ID:	48788Click image for larger version. 

Name:	1st Summation Query.png 
Views:	25 
Size:	26.8 KB 
ID:	48786
    Figure 1: Summation of Motor Loads for Each Panel


    The problem is, Panel-1 also feeds Panel-2 and Panel-3.
    I need to do another aggregation query sum for Panel-1 that sums the loads for the motor loads that Panel-1 feeds as well as sums the total loads for Panel-2 and Panel-3

    Example:
    Motor Loads on Panel-1 = 150Watts
    Motor Loads on Panel-2 = 100Watts
    Motor Loads on Panel-3 = 100Watts

    Total Load for Panel-1 = 350Watts

    I have a Table created that tracks the names of the electrical Panels and where each panel gets its power from
    Click image for larger version. 

Name:	Panel Names.png 
Views:	24 
Size:	25.3 KB 
ID:	48787
    Figure 2: Electrical Panel Names and Where they are fed from
    ------------
    I have thought about this for days now. Im very confused at this point. Im not thinking about the problem in the proper steps/framework.


    I have designed a aggregation query that provides the total load for each panel already; as seen in ​Figure 1.
    Now, I must do the final step as described above. I am STRUGGLING to form the proper approach to this problem.

    How do I do the second step of summing the load for Panel-1 that includes the sum of the motors it provides power to as well as the sum of the power it provides to Panel-2 and Panel-3?

    DATABASE CAN BE DOWNLOADED FROM THIS LINK:
    https://drive.google.com/file/d/1QUC...p=sharingThank you for any help. I hope to learn from the best.
    Attached Thumbnails Attached Thumbnails 1st Summation Query Results.png  
    Last edited by Bgum; 09-23-2022 at 03:01 PM.

  2. #2
    Bgum is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Posts
    5
    This is likely done with a SubQuery. Just not sure how to do it.

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    what you are referring to is possibly what is called a recursive action. - a record references another record in the same table which in turn references another record in the same table. Access can only do this with the use of vba OR if the maximum number of levels is known, using multiple aliased tables joined on gearname to fed from

    You should be able to identify the 'top' record - it should be the one where fedfrom is null

    so join the table (aliased A) (where fedfrom is null) using a left join to itself (aliased B) on on A.gearnname=B.fedfrom

    From your description, that is as far as it goes - you don't have 'level C' boards fed from 'level B' boards

    Now join multiple aliases of your electrical distribution table, one to each of your A and B aliased tables, Perhaps alias them as AD and BD respectively

    now you can sum the wattages of each of the distribution tables with something like

    sum(AD.KW+nz(BD.KW,0))


    without data to work with, this is just a suggestion. It may be you will need to use a mixture of avg and sum to get the right value

  4. #4
    Bgum is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Posts
    5
    Thank you for your help CJ, I appreciate you taking the time to reply.

    Im going to give a try of what you suggested tonight.
    However, Upon initial evaluation I don't believe we are aligned. Im surprised about the suggestion of using joins. I believe that this may be a 2 layer Subquery.
    Query 1 gets the direct loads for each panel. Query 2 sums the panel loads together for each panel that is fed by another. You appear to be suggesting mainly to work with tables? I am likely just not following.

    Im going to dig into this more tonight.
    Spent 12 hours on this today and Im getting more and more confused.
    ------------
    For reference you can access the database for download at this link: https://drive.google.com/file/d/1QUC...ew?usp=sharing
    ------------
    Have a great weekend everyone.
    I apologize for bothering you with my issues

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Post 1 & 4 were moderated, I'm posting to trigger email notifications.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    However, Upon initial evaluation I don't believe we are aligned. Im surprised about the suggestion of using joins. I believe that this may be a 2 layer Subquery.
    A subquery might be a solution but your description does not go far enough, so I was just covering the bases

    The problem is, Panel-1 also feeds Panel-2 and Panel-3.
    I need to do another aggregation query sum for Panel-1 that sums the loads for the motor loads that Panel-1 feeds as well as sums the total loads for Panel-2 and Panel-3
    Is a stated 'problem'

    What is missing is the background - i.e. can panel 3 feed a panel 4 which in turn might feed a panel 5?

    And as an aside, your fed_from field should be populated with the ID, not the gearname. Don't know if that is the case or not.

    There have been plenty of times when an OP states the problem but omits key information which means the solution provided does not provide the desired results.

    If only 2 'levels' Another solution may be to have two queries. One grouping on ID/gearname and with criteria to exclude those where FedFrom is populated, the other grouping on fedfrom and excluding those where fedfrom is null. Then have a 3rd query to join them together. This can all be done in one query using aliases

    Code:
    SELECT GearName, A.ttlKw+nz(B.ttlKw,0) as overallKW FROM
    (SELECT GearName, sum(KW) as ttlKW
    FROM tblPanels INNER JOIN electrical ON tblPanels.ID=electrical.panelID
    WHERE fedfrom is null
    GROUP BY GearName) A 
    LEFT JOIN
    (SELECT fedfrom, sum(KW) as ttlKW
    FROM tblPanels INNER JOIN electrical ON tblPanels.ID=electrical.panelID
    WHERE fedfrom is not null
    GROUP BY fedFrom) B
    ON A.Gearname=B.fedFrom
    Or perhaps a union query
    Code:
    SELECT GearName, sum(KW) as ttlKW FROM 
    (SELECT GearName, KW
    FROM tblPanels INNER JOIN electrical ON tblPanels.ID=electrical.panelID
    WHERE fedfrom is null
    UNION ALL SELECT fedfrom, KW
    FROM tblPanels INNER JOIN electrical ON tblPanels.ID=electrical.panelID
    WHERE fedfrom is not null)
    GROUP BY GearName
    Last edited by CJ_London; 09-24-2022 at 04:40 AM.

  7. #7
    CarlettoFed is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Dec 2019
    Posts
    255
    Instead of Motor-A and Panel-1 you should write the actual codes of the two so that you can correctly understand what the relationships are between them as from what you have written it is not clear.

  8. #8
    Bgum is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2022
    Posts
    5
    SOLVED!

    Thank you everyone for your help
    CJ I really appreciate you taking the time to help me.
    Ultimately I was not able to understand your subqueries. You are doing some pretty slick and advanced stuff there. Maybe some day I can learn how to get on your level. I didnt even know where to start with my questions back to your, which is why I never replied.

    Additionally I now see why my post was hard to understand and will make the necessary adjustments in the future for a more understandable problem statement.
    ------------
    The solution:

    I used recursion via VBA call within a Query

    Here are my notes from my journal:

    1. For testing purposes I created a table with the id, name, fed_from, grandtotal
      1. Made this via a query make
      2. Chose to work with a table rather than with a query that actively was calcing Panel's personal/immediate loads.

    2. I made a query of this table. A single calc'd field that called the recursive function
      1. This generated a childSum field. Only loads from child panels and their child panels

    3. I made another calc'd field that summed childSum and GrandTotal


    The Query that called the recursive function:
    Click image for larger version. 

Name:	query.png 
Views:	9 
Size:	9.5 KB 
ID:	48824Click image for larger version. 

Name:	result.png 
Views:	9 
Size:	28.4 KB 
ID:	48825




    Code:
    '===========================================================' 28/Sep/2022 04:45 PM[Wednesday]   AUTHOR: Brandon Gum
    '--
    'STATUS = Working
    '--
    'DESCRIPTION: Use recursion to find the total load on the panel based off what it feeds.
    'Use this within a query. Have a calc'd field that calls this and passes in the ID of the parent pnl.
    '--
    'HELPFUL LINK =
    'Post #9 is how I figured it out. Really the example is the exact same thing that I am trying to do, just with a diff Where.
    'https://www.access-programmers.co.uk/forums/threads/is-it-possible-to-create-a-recursive-sum-in-ms-access-by-sql-query-or-vba.315848/
    '===========================================================
    Public Function panelRecSum(ByVal tbl As String, parentId As Long, load As Long) As Long
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Static result As Long
        If load = 0 Then result = 0
        
        Set db = CurrentDb
        Set rs = db.OpenRecordset("Select ID, Gear_Name, Fed_From, [GrandTotal] From " & tbl & " Where Fed_From = " & parentId & ";")
        
        With rs
            If Not (.BOF And .EOF) Then
                .MoveFirst
                Do Until .EOF
                    load = load + Nz(![GrandTotal], 0) 'add the load
                    Call panelRecSum("PanelSumTest", ![ID], load) 'recursion
                .MoveNext
                Loop
            End If
            .Close
        End With
        Set rs = Nothing
        Set db = Nothing
        panelRecSum = load
    End Function

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

Similar Threads

  1. Aggregation based on subcategories
    By harryklein in forum Queries
    Replies: 3
    Last Post: 04-14-2021, 07:34 AM
  2. Replies: 3
    Last Post: 11-04-2020, 09:58 AM
  3. Aggregation function issue in Access
    By Fais in forum Access
    Replies: 7
    Last Post: 08-06-2014, 04:27 PM
  4. Aggregation Query doubt
    By akshayajmani in forum Access
    Replies: 4
    Last Post: 10-24-2012, 11:52 PM
  5. Different aggregation levels
    By mod2000 in forum Queries
    Replies: 1
    Last Post: 08-11-2011, 07:27 AM

Tags for this Thread

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