Combine Related Queries ???

[Guest]

Is there a way to compose the following queries? Query 1 selects all
employees from the Employees table, and then sums their assigned deposit
accounts from the depositPortfolio table.
===============
SELECT Employees.officerNumber, Employees.Full_Name,
Sum(CCur(Nz([balance],0))) AS Deposits
FROM Employees LEFT JOIN depositPortfolio ON Employees.officerNumber =
depositPortfolio.officerNo
GROUP BY Employees.officerNumber, Employees.Full_Name;

Query 2 does the same thing, only for the CD accounts rather than the
deposit accounts.
==========
SELECT Employees.officerNumber, Employees.Full_Name,
Sum(CCur(Nz([balance],0))) AS CDBal
FROM Employees LEFT JOIN CDs ON Employees.officerNumber = CDs.officerNo
GROUP BY Employees.officerNumber, Employees.Full_Name;

Query 3 also does the same thing, only for the depositTransfers table. When
a loan officer has a deposit account transferred to them from another loan
officer, then the balance of that transferred account has to be subtracted
from their portfolio total under the rules of this bank's incentive pay plan.
===========
SELECT Employees.officerNumber, Employees.Full_Name,
Sum(CCur(Nz([balance],0))) AS DepTransfers
FROM Employees LEFT JOIN depositTransfers ON Employees.officerNumber =
depositTransfers.officerNoTo
GROUP BY Employees.officerNumber, Employees.Full_Name;


The last query combines all of these. It adds together the deposit accounts
and CD accounts, then subtracts the transferred deposit accounts, to arrive
at a total deposit portfolio amount for each loan officer.

SELECT depositPortfolio1.officerNumber, depositPortfolio1.Full_Name,
CCur(Nz([Deposits],0)) AS DepositPort, CCur(Nz([CDBal],0)) AS CDs,
CCur(nz([DepTransfers],0)) AS DepositTXFR,
CCur(Nz([Deposits]+[CDs]-[DepositTXFR],0)) AS Total_DP,
IIf([employees].[officerNumber]=[employees].[supervisorOfficerNo],"Supv","Officer") AS SupvFilter
FROM ((depositPortfolio1 INNER JOIN depositPortfolio2 ON
depositPortfolio1.officerNumber = depositPortfolio2.officerNumber) INNER JOIN
depositPortfolio3 ON depositPortfolio2.officerNumber =
depositPortfolio3.officerNumber) INNER JOIN Employees ON
depositPortfolio3.officerNumber = Employees.officerNumber
WHERE (((depositPortfolio1.Deposits)>0)) OR (((depositPortfolio2.CDBal)>0));

=============
Many thanks!
GwenH, CIW, CWP, Master MOS
Some of my best leading men have been dogs and horses ~ Elizabeth Taylor

 

[John Vinson]

Is there a way to compose the following queries? Query 1 selects all
employees from the Employees table, and then sums their assigned deposit
accounts from the depositPortfolio table.
... <queries snipped>
The last query combines all of these. It adds together the deposit accounts
and CD accounts, then subtracts the transferred deposit accounts, to arrive
at a total deposit portfolio amount for each loan officer.

It sounds like you've answered your own question.

What do you want to do, that the last query doesn't?


John W. Vinson[MVP]

 

[Guest]

I want to combine all these queries into one. Why? Because the database I'm
working on has too dang many, and for the benefit of the end users I'd like
to combine queries whenever possible, for the sake of simplicity.

But if it can't be done, it can't be done.

Thanks!