Query Results with Null Value

[Ramtrap]

Hi all,

I've searched through various posts but can't find an answer to my question.

I have three tables. I have a Student Table, a Quiz Table, and a Test Table.

The Student Table has just student names and IDs. Quiz Table is connected by StudentID and Test Table by StudentID, as well.

I want a query to show me the results of all my students so I have a query design to show:

• FirstName (Student Table)
• LastName (Student Table)
• QuizScore (Quiz Table)
• TestScore (Test Table)

When all the information is filled in, (Mike Smith, 100 (quiz), 100 (test)) then the results will populate in my query. However, if one of them has not taken a quiz or a test, they don't show at all in the query. For example, if I had three students:

1. Mike Smith; 100 (quiz); 100 (test)
2. Bob Lee; <null> (quiz); 90 (test)
3. Jennifer Myers; 95 (quiz); <null> (test)

My query will ONLY show Mike Smith. How do I get my queries to show me everyone, to include anyone that may have a Null value in the field? I've tried criteria:

• >=0 OR IsNull
• >=0 OR Is Null

Any help would be greatly appreciated. Thank you!

[pbaldy]

I assume it would be more accurate to say that there is no record in the quiz or test table, not that there's a null value? In your query, double or right-click on the join line between tables. The appropriate selection should be obvious at that point. In SQL view, that changes INNER to RIGHT or LEFT as appropriate.

[ranman256]

You want an OUTER join. (look it up)
This will show ALL values, null and not null.

I think what has to happen is, when you create a Quiz, run an append query to all students in the class to post the Quiz and date (but not grade)
grades are filled in after, and no quiz is null.

[Ramtrap]

This is perfect! I had just gone through an Access course and forgot all about the join types when creating queries. This helps a ton. Thank you!