Not all records show with Left Join

[hceonetman]

I have a query created by the query builder in Access 2000. SEI is a table containing all employees. SAI contains absence info, including sick and vacation, etc. The query prompts for payid. I enter a valid id (the record definitely exists in SEI). The only records for the employee in SEI are for 'Vacation', which is excluded by the SQL statement. My understanding of 'Left Join' is that all records from SEI should show, even if there is no match in SAI. When I run it I get a nothing in the result.

Following is the statement created by the SQL builder:

SELECT [SEI].PayID, [SEI].SubDeptName, [SEI].LName,[SEI].FName, [SEI].Name, [SAI].ReasonID, [SAI].ReasonCat, [SAI].ReasonType, [SAI].ReasonDetails, [SAI].AbsenceDate,
FROM [SEI] LEFT JOIN [SAI] ON [SEI].PayID = [SAI].PayID
WHERE ((([SEI].PayID)=[Enter PayID]) AND (([SAI].ReasonCat)<>&quot;Vacation&quot; ;

Is it something in Access, or just my understanding of the join properties?

Thanks in advance,
HCEONETMAN

 

[mp9]

Try this:

SELECT [SEI].PayID, [SEI].SubDeptName, [SEI].LName,[SEI].FName, [SEI].Name, [SAI].ReasonID, [SAI].ReasonCat, [SAI].ReasonType, [SAI].ReasonDetails, [SAI].AbsenceDate 
FROM [SEI] LEFT JOIN [SAI] ON [SEI].PayID = [SAI].PayID
WHERE ([SAI].ReasonCat<>&quot;Vacation&quot; OR [SAI].ReasonCat Is Null);

http://www22.brinkster.com/accessory/