royalhishighness
New member
- Local time
- Today, 15:52
- Joined
- Dec 29, 2013
- Messages
- 6
HI,
Below query is working fine in SQL Server but when I am trying to run in ms access error message popup: syntax error (missing operator) in query expression 't1.[BankName]=mast.[BankName]
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName'.
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan], t2.[BankSanctionTotal], t2.[BankSanction]
FROM (SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName]
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName]
order by mast.bankname;
when I am removing 2nd left join part it is working fine and the query is:
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan]
FROM (SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName]
order by mast.bankname;
Thanks in advance.
Below query is working fine in SQL Server but when I am trying to run in ms access error message popup: syntax error (missing operator) in query expression 't1.[BankName]=mast.[BankName]
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName'.
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan], t2.[BankSanctionTotal], t2.[BankSanction]
FROM (SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName]
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS BankSanctionTotal, Sum(CMaster.BankSanAmt) AS BankSanction FROM CMaster WHERE ((CMaster.BankSanAmt) Is Not Null) GROUP BY CMaster.cbankname) AS t2 ON t2.[BankName]=mast.[BankName]
order by mast.bankname;
when I am removing 2nd left join part it is working fine and the query is:
SELECT mast.[bankname], t1.[Total], t1.[SumOfTFCLoan]
FROM (SELECT DISTINCT (CMaster.cbankname) AS BankName FROM CMaster) AS mast
LEFT JOIN (SELECT DISTINCT (CMaster.cbankname) AS BankName, Count(*) AS Total, Sum(CMaster.TFCLoan) AS SumOfTFCLoan
FROM CMaster WHERE ((CMaster.TFCLoan) Is Not Null) GROUP BY CMaster.cbankname) AS t1 ON t1.[BankName]=mast.[BankName]
order by mast.bankname;
Thanks in advance.
Last edited: