none
Converting SQL Query to Access - LEFT JOIN With AND

    Question

  • Please help convert this SQL Server 2008 query so it is Access 2010 compatible:

    SELECT QC.QCDecisionPoint.Description AS [Decision Point], QC.QCDecisionPoint.QCDecisionPointID,  QC.QCResultDecisionPoint.QCResult
    FROM ((QC.QCAttribute INNER JOIN QC.QCAttributeDecisionPointAsc ON QC.QCAttribute.QCAttributeID = QC.QCAttributeDecisionPointAsc.QCAttributeID) INNER JOIN QC.QCDecisionPoint ON QC.QCAttributeDecisionPointAsc.QCDecisionPointID = QC.QCDecisionPoint.QCDecisionPointID)
    LEFT JOIN QC.QCResultDecisionPoint ON QC.QCDecisionPoint.QCDecisionPointID=QC.QCResultDecisionPoint.QCDecisionPointID AND QC.QCResultDecisionPoint.QCAssignmentID=28
    WHERE QC.QCAttribute.Description= 'Causation'

    The underlined condition is causing a problem.  Basically, I get null QCResult value for all decision points that apply, except for those entries for certain assignments which are non-null.

    Wednesday, December 05, 2012 5:51 PM

Answers

  • The answer to re-writing this query differently can be seen in this response by Dirk:

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0b26c20b-52a8-441f-97eb-bfae5070e47d

    SELECT
     
    7674754 AS DocID,
     
    'All Supporting Documents' AS DocumentType,
      B
    .Description AS [Decision Point],
      B
    .QCDecisionPointID,
      C
    .QCNote
    FROM
     
    (QCAttributeDecisionPointAsc A
      
    INNER JOIN QCDecisionPoint B
      
    ON A.QCDecisionPointID = B.QCDecisionPointID)
    LEFT OUTER JOIN
     
    (SELECT * FROM QCResultDecisionPoint WHERE QCAssignmentID=53) C
    ON C.QCDecisionPointID=B.QCDecisionPointID
    WHERE  A.QCAttributeID= 3;

    • Marked as answer by RSNoobie Thursday, January 24, 2013 9:21 PM
    Thursday, January 24, 2013 9:21 PM

All replies

  • Change the underlined condition as follows:

    AND iif( isnull(QC.QCResultDecisionPoint.QCAssignmentID),
      0,
      QC.QCResultDecisionPoint.QCAssignmentID
    ) = 28


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Wednesday, December 05, 2012 5:56 PM
  • Thanks, Pieter, for your quick response.  Here is what it looks like in Access and I am getting the "Syntax error in join expression" error:

    SELECT QC_QCDecisionPoint.Description AS [Decision Point], QC_QCDecisionPoint.QCDecisionPointID, QC_QCResultDecisionPoint.*
    FROM ((QC_QCAttribute INNER JOIN QC_QCAttributeDecisionPointAsc ON QC_QCAttribute.QCAttributeID = QC_QCAttributeDecisionPointAsc.QCAttributeID) INNER JOIN QC_QCDecisionPoint ON QC_QCAttributeDecisionPointAsc.QCDecisionPointID = QC_QCDecisionPoint.QCDecisionPointID) LEFT JOIN QC_QCResultDecisionPoint ON QC_QCAttributeDecisionPointAsc.QCDecisionPointID = QC_QCResultDecisionPoint.QCDecisionPointID
    AND iif( isnull(QC.QCResultDecisionPoint.QCAssignmentID),
      0,
      QC.QCResultDecisionPoint.QCAssignmentID
    ) = 28
    WHERE (((QC_QCAttribute.Description)="Causation"));

    PS:

    The hard coded values will be replaced in the future  as follows:

    '28' : [Forms]![QCDecisionPoints]![QCAssignmentID]

    'Causation': [Forms]![QCClaimAttributes]![AttributesDropdown]));

    Wednesday, December 05, 2012 6:24 PM
  • Both instances of
       QC.QCResultDecisionPoint
    need to become
      QC_QCResultDecisionPoint


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Wednesday, December 05, 2012 6:44 PM
  • Also, it is possible that you will need to wrap QC_QCResultDecisionPoint in a query that performs the null coalescing (coalescement?) operation of my first post, if the join clause is still invalid.

    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    Wednesday, December 05, 2012 6:46 PM
  • Thanks, Pieter.  I am trying it out now.

    Wednesday, December 05, 2012 7:18 PM
  • Pieter, I am not getting the results I want with the iif statement.  :-(

    This is working as a filter in the where clause, I want the AND clause as part of the left join.  Basically, what I am seeing in the results is:

    For QCAssignmentID=26: (4 records)

    Decision Point : Header/Field Name
    Capacity (per heir) - which has non-empty note attached, user added it the first time when the form loaded
    Check box (review page)
    First name (per heir)
    Last name (per heir)

    For QCAssignment<>26: (3 records)

    Decision Point: Header/Field Name
    Check box (review page)
    First name (per heir)
    Last name (per heir)

    *The Capacity point is missing since there is a note attached to this point for AssignmentID=26, so no note may be added for Capacity for other AssignmentIDs!  I am trying to see if all 4 points will appear for all Assignment IDs; if there is a note already there, the form loads it(note can be edited).  If the point has no note, then the user has the option of adding it. 

    I hope I am making sense here.  Thank you for your help so far.

    Friday, December 07, 2012 5:57 PM
  • I suggest building successive temporary query objects, starting with the inner-most join, and seeing if each one is giving the result desired.

    Also, review how the query looks in the designer. The SQL syntax in Access is rather crippled relative to what we are used to in SQL Server (though often equivalent in power) so using the designer can help ensure that our intent is being recognized through the crippeld syntax.


    "Premature optimization is the root of all evil." - Knuth

    If I provoked thought, please click the green arrow

    If I provoked Aha! please click Propose as Answer

    We are here to learn, to share knowledge, and to earn points; all in about equal measure.

    Friday, December 07, 2012 6:03 PM
  • Thanks for your suggestions, Pieter.  If I arrive at a solution, I shall post it here.
    Wednesday, December 12, 2012 4:52 PM
  • cut the underline code and then paste it next to where clause as follows:

    LEFT JOIN QC.QCResultDecisionPoint ON QC.QCDecisionPoint.QCDecisionPointID=QC.QCResultDecisionPoint.QCDecisionPointID

    WHERE QC.QCAttribute.Description= 'Causation' AND QC.QCResultDecisionPoint.QCAssignmentID='28'

    or

    INNER JOIN QC.QCResultDecisionPoint ON QC.QCDecisionPoint.QCDecisionPointID=QC.QCResultDecisionPoint.QCDecisionPointID

    WHERE QC.QCAttribute.Description= 'Causation' AND QC.QCResultDecisionPoint.QCAssignmentID='28'

    • Edited by Sandra VO Wednesday, December 12, 2012 10:09 PM
    Wednesday, December 12, 2012 6:02 PM
  • Hi RSNoobie,

    Welcome to the MSDN forum.

    How is it going with the problem?

    Have a nice day.


    Yoyo Jiang[MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, December 20, 2012 7:59 AM
  • I have not been able to solve the issue. :-(

    Perhaps I should explain better.  I have a table that has AssignmentIDs.  For each Assignment, I have AttributeIDs, and each attribute has decision points attached to it.  When a note is added to any assignment's decision point, a QCNote is saved for it in the QCResultDecisionPoint table.  These values can be re-visited and re-edited.

    So, when the decision point form is opened, the QCNote value is either 'ABC' or Null for an assignmentID. I am unable to get the query below to work in Access the way it does in SQL (to get the null note decision points for those assignments that have not been reviewed yet).  Any filters in the where clause cause faulty (belong to another Assignment ID) or no records to appear.

    My SQL query looks like the one below:

    SELECT QC.QCDecisionPoint.Description AS [Decision Point], QC.QCDecisionPoint.QCDecisionPointID,   QC.QCResultDecisionPoint.QCNote
     FROM ((QC.QCAttribute INNER JOIN QC.QCAttributeDecisionPointAsc ON QC.QCAttribute.QCAttributeID = QC.QCAttributeDecisionPointAsc.QCAttributeID) INNER JOIN QC.QCDecisionPoint ON QC.QCAttributeDecisionPointAsc.QCDecisionPointID = QC.QCDecisionPoint.QCDecisionPointID)
     LEFT JOIN QC.QCResultDecisionPoint ON QC.QCDecisionPoint.QCDecisionPointID=QC.QCResultDecisionPoint.QCDecisionPointID AND QC.QCResultDecisionPoint.QCAssignmentID=60
     WHERE QC.QCAttribute.Description= 'Exigent Status Document'

    The answer to this query is two decision points, one with a note, one without.  I get one back in Access, since it does not support addition of the 'AND' clause to the LEFT JOIN.

    This is the gist of it.  There is one other table in my database.  I have a QCResultAttribute table that has a record thrown into it whenever a decision point form is opened for any assignment.  For any QCResultAttributeID, the QCResultDecisionPoint table may or may not have a record based on whether a QCNote has been added to that Assignment and that Attribute's specific decision point. 

    Now, if I add the QCResultAttribute table to the query and do a left join with the QCResultDecPt table, I get an ambiguous joins error.  If I create a subquery to use in this query, then my QCNote field becomes un-editable (is this because it is now a stacked query?)  I tried using a function to do what I need, but for now, I am getting an error saying it does not recognize ADO recordset.  Adding missing references to the object library did not fix this error.  Also, I am unable to use two independent queries to populate the one decision point form.  I admit I have very limited knowledge of Access, so I am stuck in this manner.  Any help is much appreciated.  Mind boggling to read, so I thank you if you get this far :-)


    • Edited by RSNoobie Thursday, January 03, 2013 7:40 PM
    Thursday, January 03, 2013 7:18 PM
  • Hi RSNoobie,

    If I understand correctly, sandra V O's suggestion to move the "AND" clause into the Where section, should resolve the problem for you. I tested this and I ended up with two decision points, one with a value for the note and one null.  My query is below.  I hope this helps!

    SELECT QC_QCDecisionPoint.Description AS [Decision Point], QC_QCDecisionPoint.QCDecisionPointID, QC_QCResultDecisionPoint.QCNote, QC_QCResultDecisionPoint.QCAssignmentID, QC_QCAttribute.Description
    FROM ((QC_QCAttribute INNER JOIN QC_QCAttributeDecisionPointAsc ON QC_QCAttribute.QCAttributeID = QC_QCAttributeDecisionPointAsc.QCAttributeID) INNER JOIN QC_QCDecisionPoint ON QC_QCAttributeDecisionPointAsc.QCDecisionPointID = QC_QCDecisionPoint.QCDecisionPointID) LEFT JOIN QC_QCResultDecisionPoint ON QC_QCDecisionPoint.QCDecisionPointID = QC_QCResultDecisionPoint.QCDecisionPointID
    WHERE (((QC_QCResultDecisionPoint.QCAssignmentID)=60) AND ((QC_QCAttribute.Description)='Exigent Status Document'));

     


    Sharon M, Microsoft Online Community Support

    Friday, January 04, 2013 7:48 PM
  • Thank you for your reply, Sharon.  I appreciate you all trying so hard to give me a hand with this issue. 

    Here is what I get from your query above for AssignmentID=31 and QC_QCAttribute.Description="Chest X-ray" (just the ones that have notes, the other decision points are missing):

    Decision Point QCDecisionPointID QCNote QCAssignmentID Description --Header
    Bi-Lateral parenchymal changes 3 Chest Xray 4945106 DP 3 31 Chest X-Ray
    Consistent with 10 Chest Xray 4945106 DP 10 31 Chest X-Ray
    Corresponding document 11 Chest Xray 4945106 DP 11 31 Chest X-Ray

    --(3 row(s) affected)

    I tried recently to solve this issue by using a function in SQL that would give me the right values.  Please find the function and the expected results below.  I have not been able to use this parametrized function in Access VBA to load and populate my form (don't know how, and getting too many errors along the way, plus more in my other post here !) :-(

    ALTER FUNCTION [dbo].[FnGetDecisionPoints]

     @QCAssignmentID INT,@QCAttributeID INT
    )
    RETURNS TABLE
    AS
    RETURN
    (
     -- Add the SELECT statement with parameter references here
    SELECT QC.QCDecisionPoint.Description AS [Decision Point], QC.QCDecisionPoint.QCDecisionPointID,QC.QCResultDecisionPoint.QCResult, QC.QCResultDecisionPoint.QCNote, QC.QCResultDecisionPoint.QCResultAttributeID,QCAttribute.QCAttributeID
    FROM ((QC.QCAttribute INNER JOIN QC.QCAttributeDecisionPointAsc ON QC.QCAttribute.QCAttributeID = QC.QCAttributeDecisionPointAsc.QCAttributeID) INNER JOIN QC.QCDecisionPoint ON QC.QCAttributeDecisionPointAsc.QCDecisionPointID = QC.QCDecisionPoint.QCDecisionPointID)
    LEFT JOIN QC.QCResultDecisionPoint ON QC.QCDecisionPoint.QCDecisionPointID=QC.QCResultDecisionPoint.QCDecisionPointID AND QC.QCResultDecisionPoint.QCAssignmentID=@QCAssignmentID
    WHERE QC.QCAttribute.QCAttributeID=@QCAttributeID
    )

    SELECT * FROM FnGetDecisionPoints(70,4)--AssignmentID=70, Attr:Chest X-Ray 

    (get all 16 points with null notes)

    RESULTS:

    SELECT * FROM FnGetDecisionPoints(31,4)--AssignmentID=31, Attr:Chest X-Ray

    (get all 16 points with null and not null notes)

    RESULTS:

    I am starting to think that my database design might be to blame, or perhaps Access is much harder that I thought, to use.  I have had no trouble in the past displaying forms and reports with data, but this app is taking too long to develop, what with it's inserts and updates *sigh*  Thanks to all for your help.

    Additionally, since functions cannot be linked to, any idea on how I would use this function in a query to grab the values - I was unsuccessful in doing so, kept getting syntax errors with the parameter values, and join expression not supported, etc).  Any good links on using parametrized SQL UDFs in Access VBA as the recordsource for a form?
    • Edited by RSNoobie Friday, January 11, 2013 5:52 PM
    Friday, January 11, 2013 5:49 PM
  • The answer to re-writing this query differently can be seen in this response by Dirk:

    http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/0b26c20b-52a8-441f-97eb-bfae5070e47d

    SELECT
     
    7674754 AS DocID,
     
    'All Supporting Documents' AS DocumentType,
      B
    .Description AS [Decision Point],
      B
    .QCDecisionPointID,
      C
    .QCNote
    FROM
     
    (QCAttributeDecisionPointAsc A
      
    INNER JOIN QCDecisionPoint B
      
    ON A.QCDecisionPointID = B.QCDecisionPointID)
    LEFT OUTER JOIN
     
    (SELECT * FROM QCResultDecisionPoint WHERE QCAssignmentID=53) C
    ON C.QCDecisionPointID=B.QCDecisionPointID
    WHERE  A.QCAttributeID= 3;

    • Marked as answer by RSNoobie Thursday, January 24, 2013 9:21 PM
    Thursday, January 24, 2013 9:21 PM