UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Calculate Child Sums And Compare Them To Parent Sum, Access 2013    
 
   
SomekindaVB
post Aug 6 2019, 10:03 PM
Post#1



Posts: 312
Joined: 15-December 16



Hi all,

I have a parent record with a field called 'Permitted Total'
I have a child record with a field 'Child Value'

For every Parent, I would need to see the aggregate total of the children.

e.g.

Parent Permitted Total = 100

Child 1 = 20
Child 2 = 40
Child 3 = 60

Child totals exceed Parent Permitted Total by 20.

So how would a query look that would give this to me?

Help appreciated.

Cheers
Go to the top of the page
 
WildBird
post Aug 6 2019, 10:15 PM
Post#2


UtterAccess VIP
Posts: 3,603
Joined: 19-August 03
From: Auckland, Little Australia


Easier if you had a stripped down DB to use the fields, but idea is to have a query with the ID, and grouped by SUM . Join this query and should be easy to compare.

Without knowing your schema and sample data, hard to see SQL.

--------------------
Beer, natures brain defragging tool.
Go to the top of the page
 
MadPiet
post Aug 6 2019, 10:33 PM
Post#3



Posts: 3,279
Joined: 27-February 09



Since the Child table will have the ParentID as a foreign key, you can group by Child.ParentID to group all the children of a parent together. So then you can generate a sum of the Child "values".

CODE
SELECT Child.ParentID, Parent.PermittedTotal, Sum(Child.ChildValue) AS SumOfChildValue
FROM Parent INNER JOIN Child ON Parent.ParentID = Child.ParentID
GROUP BY Child.ParentID, Parent.PermittedTotal;

Then you just subtract in another query.

Parent.PermittedTotal - SUM(Child.ChildValue)
and group by ParentID.

Next query:
CODE
SELECT TotalPermitted_vs_ChildTotal.ParentID, TotalPermitted_vs_ChildTotal.PermittedTotal, TotalPermitted_vs_ChildTotal.SumOfChildValue, [PermittedTotal]-[SumOfChildValue] AS OverUnder
FROM TotalPermitted_vs_ChildTotal;


like falling off a log, right?
This post has been edited by MadPiet: Aug 6 2019, 10:42 PM
Go to the top of the page
 
SomekindaVB
post Aug 6 2019, 11:46 PM
Post#4



Posts: 312
Joined: 15-December 16



Thanks all. I was avoiding making a 2 query solution, but it seems this may be the only way. I did that and it appears to function correctly.

Thanks for helps

Go to the top of the page
 
dale.fye
post Aug 7 2019, 05:17 AM
Post#5



Posts: 160
Joined: 28-March 18
From: Virginia


You don't need two queries, this should work:

CODE
SELECT Child.ParentID, Parent.PermittedTotal
, Sum(Child.ChildValue) AS SumOfChildValue
, Parent.PermittedTotal - SUM(Child.ChildValue) as UnderOver
FROM Parent INNER JOIN Child ON Parent.ParentID = Child.ParentID
GROUP BY Child.ParentID, Parent.PermittedTotal;

This post has been edited by dale.fye: Aug 7 2019, 05:18 AM

--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th September 2019 - 11:43 AM