Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Unanswered: problem in self join query

    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.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The Jet Engine of Access is more limited than the Query Engine of SQL Server. Create 2 queries in Access. The first named t1 with:
    Code:
    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)
    Then the second with:
    Code:
    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 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;
    Note: I did not verify the validity of the queries. Post the Tables definition if you want me to.
    Have a nice day!

  3. #3
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    I think JHB gave you the answer here:

    problem in self join query - Access World Forums

    Wayne

  4. #4
    Join Date
    Dec 2013
    Posts
    2

    Thumbs up problem in self join query

    Yes! JHB given perfect solution. Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •