Solved

Sum the values from 2 different queries in 1 query

Posted on 2013-06-13
2
448 Views
Last Modified: 2013-06-13
Hi Experts

I have the 2 queries below, each of which gives me one number.  I want to be able to create a query which uses these 2 queries as subqueries so the 2 values can be summed and I have one total.  Can you help?

SELECT Sum([Pnl by GL - Accounting result].[SumOf7 Total Pnl YTD]) AS GL_ACCOUNTING
FROM [Pnl by GL - Accounting result]
WHERE ((([Pnl by GL - Accounting result].ReportCategory)="HTM" Or ([Pnl by GL - Accounting result].ReportCategory)="Loans&Receivables") AND (([Pnl by GL - Accounting result].AcctArea)="BA_AMADEUS" Or ([Pnl by GL - Accounting result].AcctArea)="BA_TOP_UP" Or ([Pnl by GL - Accounting result].AcctArea)="BA_CDS_HDG" Or ([Pnl by GL - Accounting result].AcctArea)="HENDERSON") AND (([Pnl by GL - Accounting result].AccountCode)=5865000000));

and

SELECT Sum([PnL by GL - Interest].[SumOf11 TotalInterest]) AS GL_INTEREST
FROM [PnL by GL - Interest]
WHERE ((([PnL by GL - Interest].ReportCategory)="Loans&Receivables" Or ([PnL by GL - Interest].ReportCategory)="HTM") AND (([PnL by GL - Interest].AcctArea)="BA_AMADEUS" Or ([PnL by GL - Interest].AcctArea)="BA_TOP_UP" Or ([PnL by GL - Interest].AcctArea)="BA_CDS_HDG" Or ([PnL by GL - Interest].AcctArea)="HENDERSON"));
0
Comment
Question by:simsima_7876
2 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 360 total points
ID: 39244083
Select q1.GL_ACCOUNTING+q2.GL_INTEREST as Mytotal from

(SELECT Sum([Pnl by GL - Accounting result].[SumOf7 Total Pnl YTD]) AS GL_ACCOUNTING
FROM [Pnl by GL - Accounting result]
WHERE ((([Pnl by GL - Accounting result].ReportCategory)="HTM" Or ([Pnl by GL - Accounting result].ReportCategory)="Loans&Receivables") AND (([Pnl by GL - Accounting result].AcctArea)="BA_AMADEUS" Or ([Pnl by GL - Accounting result].AcctArea)="BA_TOP_UP" Or ([Pnl by GL - Accounting result].AcctArea)="BA_CDS_HDG" Or ([Pnl by GL - Accounting result].AcctArea)="HENDERSON") AND (([Pnl by GL - Accounting result].AccountCode)=5865000000))) as q1,

(SELECT Sum([PnL by GL - Interest].[SumOf11 TotalInterest]) AS GL_INTEREST
FROM [PnL by GL - Interest]
WHERE ((([PnL by GL - Interest].ReportCategory)="Loans&Receivables" Or ([PnL by GL - Interest].ReportCategory)="HTM") AND (([PnL by GL - Interest].AcctArea)="BA_AMADEUS" Or ([PnL by GL - Interest].AcctArea)="BA_TOP_UP" Or ([PnL by GL - Interest].AcctArea)="BA_CDS_HDG" Or ([PnL by GL - Interest].AcctArea)="HENDERSON"))) as q2
0
 

Author Closing Comment

by:simsima_7876
ID: 39244092
Thanks very much.  Super quick!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

840 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