Results 1 to 9 of 9
  1. #1
    Whizbang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    37

    [Solved] Left Join That Includes Null values on Right Table, Even If Match Found

    Ok, so I have a table that lists all possible questions. I then have a table of answers for those questions. Each user gets the entire list of questions in a continuous form. I would like to join the answers table to the questions table. Here is my join statement.

    FROM tblQuestions LEFT JOIN tblAnswers ON (tblQuestions.[Question ID] = tblAnswers.[Question ID])

    My problem is that if I then add

    WHERE tblAnswers.[User] = [Forms]![frmSurvey]![User] OR tblAnswers.[User] Is Null



    And this particular user hasn't answered the question yet, but another user has, the query will not display the question at all for that user.

    I want to get something like this in datasheet view (this would be minus the WHERE clause)

    Code:
    Question ID     Question     Answer ID     Question ID     Answer     User
    1               Blah?        1             1               Blah.      User1
    1               Blah?        {Null}
    2               Blah2?       2             2               Blah.      User1
    2               Blah?        {Null}
    This would allow User2 to answer the question

    But I only get this
    Code:
    Question ID     Question     Answer ID     Question ID     Answer     User
    1               Blah1?       1             1               Blah.      User1
    2               Blah2?       2             2               Blah.      User1
    If I add the WHERE clause, I would get no results, but I want it to list all the questions anyway, regardless if it has been answered before.
    Last edited by Whizbang; 11-01-2011 at 01:33 PM. Reason: Solved

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The only way for a blank record for each answer to be returned in query is if records exist in tblAnswers. If they don't then will not show in the query for that user. If it is that user's ID used as criteria then no records will show.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    boblarson is offline --------
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2011
    Posts
    1,272
    You cannot use criteria on any fields that are in the table being used as the Left Join (the only that work are Is Null or Is Not Null).

    I don't see a way to do this without appending a full set of question ID's to the tblAnswers table first so that there is a place to store the user's answers.

  4. #4
    Whizbang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    37
    How would you approach this situation, where you want the questions to be dynamic and changeable (i.e. have an administrator use a form to input questions), and then be able to use those questions on a one to many relationship with the answers supplied by many people?

    I could have the user select each question and then input the answer, but this could lead to users neglecting to answer a question.

    I guess I would have to resort to VBA and do a loop:
    Code:
    'psuedocode
    For Each Record in QuestionsTable
          IF DCOUNT([Answer ID], "AnswerTable", "[User] = User AND [Question ID] = Record.Question_ID") = 0 Then
               'insert new record into Answers table for Question
         End If
    Next Record

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Give it a try.

    The DCount structure is wrong, presuming User and Record.Question_ID are inputs:
    DCount("[Answer ID]", "AnswerTable", "[User] = '" & Me!User & "' AND [Question ID] = " & Me![Record.Question_ID])

    or use:

    If IsNull(DLookup("[Answer ID]", "AnswerTable", "[User] = '" & Me!User & "' AND [Question ID] = " & Me![Record.Question_ID])) Then
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Whizbang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    37
    I think I am going to settle on running this SQL statement when a user opens the form for the first time:

    Code:
    INSERT INTO tblAnswers([Question ID], [User])
    SELECT tblQuestions.[Question ID], DBUser() as [User]
    FROM tblQuestions;
    DBUser() is a function I made:
    Code:
    Function DBUser() as String
         DBUser = Environ("Username")
    End Function

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    How do you know it is the first time they opened form? Should you check table to make sure records not already existing?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    Whizbang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    37
    Yes, I will do some sort of count or something to verify this user has not already started/completed the questionaire before I run the statement.

  9. #9
    Whizbang is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Posts
    37
    I am such a dope. Instead of linking tblQuestions to tblAnswers and then filtering by User, I just needed to create a query for tblAnswers that filters for User.

    So, now I just...
    FROM tblQuestions LEFT JOIN qryAnswers ON (tblQuestions.[Question ID] = tblAnswers.[Question ID])

    I cannot believe it took me this long to come to this very simple solution.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Left Join not
    By j_a_monk in forum Queries
    Replies: 5
    Last Post: 08-07-2011, 09:47 AM
  2. Replies: 0
    Last Post: 07-26-2011, 02:03 PM
  3. Replies: 3
    Last Post: 02-02-2011, 01:00 PM
  4. Outer left join leaves out null values
    By mschles4695 in forum Queries
    Replies: 1
    Last Post: 12-22-2010, 11:43 PM
  5. Replies: 1
    Last Post: 12-02-2010, 04:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums