# 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?

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

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

Author Commented:
Thank you very much
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.