# how to perform the calculation on two numbers in the joint query if numbers from different queries

I have a very big joint query. What you see only two lines I have a problem with:

SELECT "1", "Total Requested"  ,[Res Total IVR]+ [ResTotal Rep]  +[Non-Res Total IVR] +[Non-ResTotal Rep] from qrySummaryRec_1;

UNION ALL

SELECT "2", "Total Not Approved", qryCancelSummary_3.NotApproved  FROM qryCancelSummary_3;

results is :
1                Total requested          30,000
2                 Total Not Approved  20,000

how to add  a new line to get the following

1                Total requested          30,000
2                 Total Not Approved  20,000
3                 Total Approved          10,000     '   30000-20000
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Create a new query to calculate just the approved items.

Then in your UNION query (what you describe as a big join query) add a new UNION ALL line and a second line for the new query, similar to what was done for the Total Not Approved query.
0
Database ExpertCommented:

``````SELECT "1" a , "Total Requested" request ,[Res Total IVR]+ [ResTotal Rep]  +[Non-Res Total IVR] +[Non-ResTotal Rep] amount , '' from qrySummaryRec_1;

UNION ALL

SELECT "2", "Total Not Approved", qryCancelSummary_3.NotApproved , ''  FROM qryCancelSummary_3;

UNION ALL

SELECT "3", "Total Approved" , (
(SELECT [Res Total IVR]+ [ResTotal Rep]  +[Non-Res Total IVR] +[Non-ResTotal Rep]
FROM  qrySummaryRec_1) - (SELECT qryCancelSummary_3.NotApproved FROM qryCancelSummary_3) )
, ( CONCAT ( (SELECT [Res Total IVR]+ [ResTotal Rep]  +[Non-Res Total IVR] +[Non-ResTotal Rep]
FROM  qrySummaryRec_1), '-' , (SELECT qryCancelSummary_3.NotApproved FROM qryCancelSummary_3) ) )
``````
0
Commented:
Usually we do such calculations in columns. First column - Total requested, Second - Total Not Approved, Last - Total Approved:
``````SELECT [Res Total IVR]+[Res Total Rep]+[Non-Res Total IVR]+[Non-ResTotal Rep] AS [Total requested], qryCancelSummary_3.NotApproved AS [Total Not Approved], [Res Total IVR]+[Res Total Rep]+[Non-Res Total IVR]+[Non-ResTotal Rep]-[NotApproved] AS [Total Approved]
FROM qryCancelSummary_3, qrySummaryRec_1
``````
0

Experts Exchange Solution brought to you by