asked on

Fix a sql query

I am trying to replace a query that refers to another query, to be done within the same query.

the original query is

use PBJ_2BE
SELECT tblControlTicket.ControlTicketNum, 
qry0BankDeposits_Summary.SumOfDepositTotal AS TotalDeposits, 


FROM (((((((tblControlTicket 
LEFT JOIN qry0BankDeposits_Summary ON tblControlTicket.ControlTicketNum = qry0BankDeposits_Summary.ControlTNum) 


WHERE (((tblControlTicket.PaymentTypeID)=1))

Open in new window

now the code for query "qry0BankDeposits_Summary" is as follows

SELECT tblPOS_BankDeposits.ControlTNum, Sum(tblPOS_BankDeposits.DepositTotal) AS SumOfDepositTotal
FROM tblPOS_BankDeposits
GROUP BY tblPOS_BankDeposits.ControlTNum

Open in new window

can somebody help to create a query that incorporates both of them?

thanks

damixa

ASKER

If this helps, the first one is an Access query that I am trying to convert it to a SQL server query.

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

That basically uses your original query name as an alias. You can also shorten it like this:

SELECT tblControlTicket.ControlTicketNum, 
q.SumOfDepositTotal AS TotalDeposits, 


FROM (((((((tblControlTicket 
LEFT JOIN 

(SELECT tblPOS_BankDeposits.ControlTNum, Sum(tblPOS_BankDeposits.DepositTotal) AS SumOfDepositTotal
FROM tblPOS_BankDeposits
GROUP BY tblPOS_BankDeposits.ControlTNum) q
ON tblControlTicket.ControlTicketNum = q.ControlTNum) 


WHERE (((tblControlTicket.PaymentTypeID)=1))

Open in new window

mbizup

Cleaning it up a bit more:

SELECT t.ControlTicketNum, 
q.SumOfDepositTotal AS TotalDeposits


FROM tblControlTicket t
LEFT JOIN 

(SELECT d.ControlTNum, Sum(d.DepositTotal) AS SumOfDepositTotal
FROM tblPOS_BankDeposits d
GROUP BY d.ControlTNum) q
ON t.ControlTicketNum = q.ControlTNum
WHERE t.PaymentTypeID =1

Open in new window

damixa

ASKER

Thanks perfect! Much appreciated!