Union Query Help - Sum on IDs

Experts,

I have created a Union query and I need to sum the amounts where the tblDraws_Details1.ID = tblRepayments.DrawIDrpmt
I know that I need to group based on the ID and how to do this in SQL is escaping me.  

I am greatful for you help.  
Please see attached union query and I think you can see what I am trying to do.  You can see it is sorted on ID and I need to Group those IDs and the sum will return.

here is the sql as I have it:
SELECT tblDraws_Details1.ID, Sum(tblDraws_Details1.Amount) AS SumOfAmount,  "" as PaymentMadeDummy
FROM tblDraws_Details1
GROUP BY tblDraws_Details1.ID

UNION ALL SELECT tblRepayment.DrawIDrpmt, Sum(tblRepayment.Amount) AS SumOfAmount, tblRepayment.PaymentMadeYN
FROM tblRepayment
GROUP BY tblRepayment.DrawIDrpmt, tblRepayment.PaymentMadeYN
HAVING (((tblRepayment.PaymentMadeYN)="Yes"));
Summing.accdb
pdvsaProject financeAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
If reusing your UNION query (you should name it differently) it's only this:
SELECT 
    [UNION].ID, 
    Sum([UNION].SumOfAmount) AS SumAmounts
FROM 
    [UNION]
GROUP BY 
    [UNION].ID;

Open in new window

/gustav
0
 
Gustav BrockCIOCommented:
I think what you need is an additional query to sum the union output:
SELECT 
    UNION.ID, 
    Sum(UNION.SumOfAmount) AS SumAmounts, 
    UNION.PaymentMadeDummy
FROM 
    [UNION]
GROUP BY 
    UNION.ID, 
    UNION.PaymentMadeDummy;

Open in new window

/gustav
0
 
pdvsaProject financeAuthor Commented:
Hi Gustav, thanks for the response.  I followed the suggestion as above however the amounts do not appear to be summed for where tblDraws_Details1.ID = tblRepayments.DrawIDrpmt

Do you happen to see what the issue might be?

heres a screen shot:
screenprint
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Gustav BrockCIOCommented:
I think you need the same as in your other question: a query that use the above query as source, then group by ID and sum SumAmounts.

/gustav
0
 
pdvsaProject financeAuthor Commented:
queries like that are beyond my skills. I will keep the question open for a bit and maybe i will get lucky.
0
 
pdvsaProject financeAuthor Commented:
Perfect.  Thanks for hanging in there.  I need to take a break.
0
 
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.