Solved

Union Query Help - Sum on IDs

Posted on 2016-08-19
7
34 Views
Last Modified: 2016-08-20
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
0
Comment
Question by:pdvsa
  • 4
  • 3
7 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41763424
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
 

Author Comment

by:pdvsa
ID: 41763433
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
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41763511
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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:pdvsa
ID: 41763525
queries like that are beyond my skills. I will keep the question open for a bit and maybe i will get lucky.
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41763534
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
 

Author Closing Comment

by:pdvsa
ID: 41763546
Perfect.  Thanks for hanging in there.  I need to take a break.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 41763551
You are welcome!

/gustav
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question