Sum a field across multiple related records 2

[bobomutt]

Greetings - trying to modify an access table/create a query to compare multiple records against a parent, and sum the values of a field and list only one record - the parent record. The parent record, when one exists for a WO#, is listed in the "Parent" column, and the actual parent record can be found in the "WO#" column. In the example shown in the screen capture - Row's 147 and 148 list WO# 12346208 as the parent, which is row 149. The result I am trying to develop is a table that only shows the parent record (12346208) with the "hours" values from all three records (row's 147,148,149)summed and displayed as the "hours" value for the parent record (so should only show row 149, with the hours= 2+2+5=9).

 

[dhookom]

I would create a union query

SELECT Parent, Hours 
FROM 2016Fleet55
WHERE Parent Is Not Null
UNION 
SELECT [WO#], Hours
FROM 2016Fleet55
WHERE Parent Is Null;

You can then create a totals query based on the Union query.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[Andrzejek]

Based on Duane's suggestion, "a totals query based on the Union query":
[pre][blue]
Select Parent, SUN(Hours) As TotalHours
From
([/blue]SELECT Parent, Hours
FROM 2016Fleet55
WHERE Parent Is Not Null
UNION
SELECT [WO#], Hours
FROM 2016Fleet55
WHERE Parent Is Null[blue])
Group By Parent[/blue]
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[bobomutt]

Both of those were great. I did a check, and in both cases, it only summed up one of the child records (child WO's) so that the total hours for record 12346208 showed as 7 instead of 9.

How I corrected this: I added "ID" within the Union selects. Why I did this (no clue if this is correct thinking or not) - in the recommended code provided, the union selects have no unique identifiers in the first select half: there would be two records with the same Parent and same Hours (both had 2 hours). So Access only collected the first one.


Select Parent, SUN(Hours) As TotalHours
From
(SELECT Parent, Hours
FROM 2016Fleet55
WHERE Parent Is Not Null
UNION
SELECT [WO#], Hours
FROM 2016Fleet55
WHERE Parent Is Null)
Group By Parent

So I added "ID" to the union select statements and it ended up with the appropriate results for total hours for 12346208 - which was 9 hours:

Select Parent, SUM(Hours) As TotalHours
From
(SELECT ID, Parent, Hours
FROM 2016Fleet55
WHERE Parent Is Not Null
UNION
SELECT ID, [WO#], Hours
FROM 2016Fleet55
WHERE Parent Is Null)
Group By Parent

Does this make sense? Really appreciate it!

Best,

Doug

 

[dhookom]

I should have posted to use UNION ALL rather than UNION. By default using only UNION will return only unique values. My bad...

SELECT Parent, Hours 
FROM 2016Fleet55
WHERE Parent Is Not Null
UNION  ALL
SELECT [WO#], Hours
FROM 2016Fleet55
WHERE Parent Is Null;

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016