Solved

Union Query Help - Sum on IDs

Posted on 2016-08-19
7
30 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

862 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

Need Help in Real-Time?

Connect with top rated Experts

28 Experts available now in Live!

Get 1:1 Help Now