Solved

Union Query Help - Sum on IDs

Posted on 2016-08-19
7
45 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 51

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 51

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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 51

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 51

Expert Comment

by:Gustav Brock
ID: 41763551
You are welcome!

/gustav
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 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