asked on

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

Gustav Brock

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

pdvsa

ASKER

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: