Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Union Query Help - Sum on IDs

Posted on 2016-08-19
7
Medium Priority
?
54 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 52

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 52

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 52

Expert Comment

by:Gustav Brock
ID: 41763551
You are welcome!

/gustav
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

876 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