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
rfedorovAsked:
Who is Participating?
 
als315Connect With a Mentor 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

Open in new window

0
 
Shaun KlineConnect With a Mentor Lead Software EngineerCommented:
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
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
Please use this-

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) ) )

Open in new window

0
 
rfedorovAuthor 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.

All Courses

From novice to tech pro — start learning today.