Query to Aggregate Cumulative Quantity of Parent and Child Entity Data

Question

How can I make a query that aggregates data from entities and their children down to the lowest level?

I have two tables.

The first is tblEntity, a self-join table with entity and parent entity relationship:

tblEntity

EntityID	Entity	PrntEntID
1	A
2	B	1
3	C	1
4	D	2
5	E	4
6	F	4

The second related table is tblEntWdg, a list of widgets assigned to entities with widget quantities:

tblEntWdg

EntWdgID	EntityID	Widget	Qty
1	5	Widget1	1
2	5	Widget2	5
3	5	Widget3	2
4	6	Widget1	1
5	6	Widget2	5
6	6	Widget3	2
7	4	Widget4	1
8	3	Widget5	5
9	2	Widget6	2

I want to make a query that aggregates the cumulative quantity of widgets of each entity and their children down to the lowest level. Below is an example:

qryEntWdgSum

EntityID	Widget	Qty
1	Widget1	2
1	Widget2	10
1	Widget3	4
1	Widget4	1
1	Widget5	2
1	Widget6	7
2	Widget1	2
2	Widget2	10
2	Widget3	4
2	Widget4	1
2	Widget6	7
3	Widget5	2
4	Widget1	2
4	Widget2	10
4	Widget3	4
4	Widget4	1
5	Widget1	1
5	Widget2	5
5	Widget3	2
6	Widget1	1
6	Widget2	5
6	Widget3	2

My current solution is to create a SELECT query that determines entity levels up to great-great-grandparent.

qryEntLvl

EntityID	ParentID	GrndPrntID	GrtGrndPrntID	GrtGrtGrndPrntID
1
2	1
3	1
4	2	1
5	4	2	1
6	4	2	1

I then use the above query in a UNION query based on tblEntWdg using each field as the EntityID field for each SELECT query in the UNION query. In a second separate SELECT query, I then have to GROUP the EntityID and Widget fields and SUM the Qty to consolidate duplicates.

The end result is like the qryEntWdgSum above, but with a few problems:

1. I have a five-level limit to my hierarchy - I'd like a solution that could handle any number of levels
2. It all seems really inefficient - my gut tells me I could use VBA recursion instead of having to resort to the qryEntLvl query and possibly avoid the UNION query, but I don't know how to proceed.
3. I get runtime errors on some of my forms I believe because I'm running out of memory as other tables and queries are joined in addition to the ones mentioned above - as a workaround I've resorted to appending the data to temporary tables which also seems inefficient.

Answer 1

It looks like there is a better answer here: Sql Sum of child Items but my sql skills weren't up getting that one to work. my solution uses a calculated field based on an isChild function. 1. add a code module using the create tab on the ribbon

Public Function isChild(ParentID As Long, ChildID As Long) As Long
'a Parent is considered their own child to simplify calculations.
Dim Parent, i As Long 'loops i generations to protect against infinite loops
Parent = ChildID
Do Until (Parent = ParentID) Or (Parent = 0) Or (i = 20)
Parent = Nz(DLookup("PrntEntityID", "TblEntity", "EntityID = " & Parent), 0)
i = i + 1
Loop

If i = 20 Or Parent = 0 Then
isChild = 0
Else
isChild = 1
End If
End Function

qryParentsChildren crosses all combination of EntityID and then uses isChild to determine if the Parent Child combination works. In the query designer drag tblEntity twice and make sure there is no relationship arrow between tblEntity and it's dupe tblEntity_1.

'sql
SELECT tblEntity.EntityID AS Parent, tblEntity_1.EntityID AS Child, isChild([tblEntity].[EntityID],[tblEntity_1].[EntityID]) AS isChild
FROM tblEntity, tblEntity AS tblEntity_1
WHERE (((isChild([tblEntity].[EntityID],[tblEntity_1].[EntityID]))=1))
ORDER BY tblEntity.EntityID, tblEntity_1.EntityID;

----------------------------------------------------------------
|       Parent       |       Child        |      isChild       |
----------------------------------------------------------------
|                  1 |                  1 |                  1 |
----------------------------------------------------------------
|                  1 |                  2 |                  1 |
----------------------------------------------------------------
|                  1 |                  3 |                  1 |
----------------------------------------------------------------
|                  1 |                  4 |                  1 |
----------------------------------------------------------------
|                  1 |                  5 |                  1 |
----------------------------------------------------------------
|                  1 |                  6 |                  1 |
----------------------------------------------------------------
|                  2 |                  2 |                  1 |
----------------------------------------------------------------
|                  2 |                  4 |                  1 |
----------------------------------------------------------------
|                  2 |                  5 |                  1 |
----------------------------------------------------------------
|                  2 |                  6 |                  1 |
----------------------------------------------------------------
|                  3 |                  3 |                  1 |
----------------------------------------------------------------
|                  4 |                  4 |                  1 |
----------------------------------------------------------------
|                  4 |                  5 |                  1 |
----------------------------------------------------------------
|                  4 |                  6 |                  1 |
----------------------------------------------------------------
|                  5 |                  5 |                  1 |
----------------------------------------------------------------
|                  6 |                  6 |                  1 |
----------------------------------------------------------------

Open the query designer again and add qryParentsChildren and tblEntity. In the query designer right click to get the context menu and select totals to set up a standard totals query:

'sql
SELECT qryParentsChildren.Parent AS EntityID, tblEntWdg.Widget, Sum(tblEntWdg.Qty) AS Qty
FROM qryParentsChildren INNER JOIN tblEntWdg ON qryParentsChildren.Child = tblEntWdg.EntityID
GROUP BY qryParentsChildren.Parent, tblEntWdg.Widget;

----------------------------------------------------------------
|      EntityID      |       Widget       |        Qty         |
----------------------------------------------------------------
|                  1 | W1                 |                  2 |
----------------------------------------------------------------
|                  1 | W2                 |                 10 |
----------------------------------------------------------------
|                  1 | W3                 |                  4 |
----------------------------------------------------------------
|                  1 | W4                 |                  1 |
----------------------------------------------------------------
|                  1 | W5                 |                  5 |
----------------------------------------------------------------
|                  1 | W6                 |                  2 |
----------------------------------------------------------------
|                  2 | W1                 |                  2 |
----------------------------------------------------------------
|                  2 | W2                 |                 10 |
----------------------------------------------------------------
|                  2 | W3                 |                  4 |
----------------------------------------------------------------
|                  2 | W4                 |                  1 |
----------------------------------------------------------------
|                  2 | W6                 |                  2 |
----------------------------------------------------------------
|                  3 | W5                 |                  5 |
----------------------------------------------------------------
|                  4 | W1                 |                  2 |
----------------------------------------------------------------
|                  4 | W2                 |                 10 |
----------------------------------------------------------------
|                  4 | W3                 |                  4 |
----------------------------------------------------------------
|                  4 | W4                 |                  1 |
----------------------------------------------------------------
|                  5 | W1                 |                  1 |
----------------------------------------------------------------
|                  5 | W2                 |                  5 |
----------------------------------------------------------------
|                  5 | W3                 |                  2 |
----------------------------------------------------------------
|                  6 | W1                 |                  1 |
----------------------------------------------------------------
|                  6 | W2                 |                  5 |
----------------------------------------------------------------
|                  6 | W3                 |                  2 |
----------------------------------------------------------------

these results are slightly different than your expected output but seem correct based on the sample data and my understanding of the question