If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combine Related Queries ???
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 |
#2
|
|||
|
|||
Combine Related Queries ???
On Sun, 19 Nov 2006 13:00:01 -0800, Gwen H
wrote: 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] |
#3
|
|||
|
|||
Combine Related Queries ???
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! -- GwenH, CIW, CWP, Master MOS Some of my best leading men have been dogs and horses ~ Elizabeth Taylor "John Vinson" wrote: On Sun, 19 Nov 2006 13:00:01 -0800, Gwen H wrote: 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] |
Thread Tools | |
Display Modes | |
|
|