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
damixaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

damixaAuthor Commented:
If this helps, the first one is an Access query that I am trying to convert it to a SQL server query.
0
mbizupCommented:
Try this:

SELECT tblControlTicket.ControlTicketNum, 
qry0BankDeposits_Summary.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) qry0BankDeposits_Summary 
ON tblControlTicket.ControlTicketNum = qry0BankDeposits_Summary.ControlTNum) 


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

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
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

0
mbizupCommented:
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

0
damixaAuthor Commented:
Thanks perfect! Much appreciated!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Virtual Server

From novice to tech pro — start learning today.