Bug: Outer join expressions retrieved wrongly

Access evaluates expressions on the outer side of a join incorrectly.

An outer join lets you select all records from one table, even if the other table has no matches. For the records with no match, all fields from the outer side of the join will be Null. At least they should be: Access gets it wrong for calculated fields.

This example demonstrates the bug with the Northwind sample database.

Open the Northwind.
Open the Orders table, and delete the employee from a couple of records.
Create a new query, and paste this into SQL View:

SELECT Orders.OrderID,
CurrentStaff.EmployeeID,
CurrentStaff.FullName
FROM Orders LEFT JOIN
    (SELECT Employees.EmployeeID,
   [FirstName] & "." & [LastName] AS FullName
    FROM Employees) AS CurrentStaff
ON Orders.EmployeeID = CurrentStaff.EmployeeID
WHERE (CurrentStaff.EmployeeID Is Null);

In design view, the query looks like this:

Query screenshot

If outer joins are new, the arrow-head on the line joining the two tables indicates the direction of the join. Double-click the line to get the Join Properties dialog (shown above.) It explains the meaning of the left join.

The criteria limits the query to those orders that have no EmployeeID. For these records, ALL fields from CurrentStaff will be Null if the query works correctly. Instead, Access returns the dot for the FullName field.

The error appears to be with the JET query optimizer. It should retrieve the records form the main query, and look for matches in the lower level query. If there is no matching EmployeeID, it should return Null; if there is a match, it should return the FullName expression (which could validly be dot if you had an employee with no name at all.) Instead, it behaves as if it is evaluating the expression after it has returned the results from the lower-level query.

JET still gets it wrong if you use a saved query (rather than a subquery as above.)

Home

Index of tips

Top