Is Null Problem on querying from one table to another

[Gavroche00]

Hi all,

I am getting more and more confused and I thought I knew Access

I have a master list of all student ID's which is the primary key from that table. I have left joined that table to the grade table (which has student ID's, grade and date). I want all the students to return a grade for a given day if there is one, and otherwise to leave the grade field blank.

It seems to me that if I select a day for which there are some students who don't have test results, I should get their ID's as well as a blank cell for the grade result. Instead, they don't appear at all. can anyone tell me why? I did left Join frocing all the records in the student table.

Thank you

David

 

[lespaul]

what's the SQL for your query?

leslie

 

[PHV]

It would help if you post your sql code ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[JonFer]

What about that SQL...

One thing you need to know is that the LEFT JOIN works before the WHERE clause with your date criteria. So after the LEFT JOIN is applied, you have every student in the master table whether or not they have a record in the grades table. If they don't have any grade records, the date will be NULL. When the WHERE clause is applied, students without any grades don't show because their date is NULL.

You need to filter the grades table first with a parameter for the date you want, save that as a query, and then LEFT JOIN the master table to the query.

If you are using Access 2003/XP, I think you can do this in one step but not with the Query Design window:

Select a.StudentID, a.Student, b.GradeDate, b.Grade
From Students as a Left Join
     (Select * from StudentGrades where GradeDate=[Enter Date]) as b on a.StudentID=b.StudentID