I've written a large-ish query with two subqueries. The query looks at incomplete mandatory training for staff members.



These two subqueries are generated from Public Functions which are used (and work) in other queries.

The names of the two subqueries are StaffDetail and CompulsoryCourse.

The query looks like this:
Code:
SELECT StaffDetail.StaffId, StaffDetail.Name, CompulsoryCourse.MandatoryTrainingId, StaffDetail.JobTitle, CompulsoryCourse.CourseId, StaffDetail.Clinical
FROM 
(
SELECT CourseGroup.CourseId, CourseGroup.MandatoryTrainingId, 0 AS [Clinical] FROM (Course INNER JOIN CourseGroup ON Course.ID = CourseGroup.CourseId) INNER JOIN MandatoryTraining ON CourseGroup.MandatoryTrainingId = 
MandatoryTraining.Id WHERE (((MandatoryTraining.Obsolete) = 0) And ((Course.Obsolete) = 0))AND (Course.CompulsoryAdmin <> 0 OR Course.CompulsoryAll <> 0) 
UNION 
SELECT CourseGroup.CourseId, CourseGroup.MandatoryTrainingId, 1 AS [Clinical] FROM (Course INNER JOIN CourseGroup ON Course.ID = CourseGroup.CourseId) INNER JOIN MandatoryTraining ON CourseGroup.MandatoryTrainingId = 
MandatoryTraining.Id WHERE (((MandatoryTraining.Obsolete) = 0) And ((Course.Obsolete) = 0)) AND (Course.CompulsoryClinical <> 0 OR Course.CompulsoryAll <> 0)
) AS CompulsoryCourse INNER JOIN 
(
SELECT Staff.ID AS StaffId, Person.Forename & ' ' & Person.Surname AS [Name], Person.JobTitle, Person.Clinical 
FROM Staff INNER JOIN Person ON Staff.PersonId = Person.ID 
WHERE Staff.Id=99
) AS StaffDetail ON CompulsoryCourse.Clinical = StaffDetail.Clinical
The problem is that this doesn't return any results (it should there is a member of staff with Staff.Id = 99)

If I remove the WHERE clause then I get results returned for all staff (including Staff.Id = 99)

If I change the WHERE clause to
Code:
WHERE Staff.Id > 98
then I get results returned for some staff (again, including Staff.Id = 99)


If I change the WHERE clause to
Code:
WHERE Staff.Id < 100
I get results returned for all staff (again, including Staff.Id = 99)

If I change the WHERE clause to
Code:
WHERE Staff.Id > 98 AND Staff.Id < 100
I get no results returned.

This is very odd.

If I take the subquery out of the bigger query
Code:
 SELECT Staff.ID AS StaffId, Person.Forename & ' ' & Person.Surname AS [Name], Person.JobTitle, Person.Clinical 
FROM Staff INNER JOIN Person ON Staff.PersonId = Person.ID 
WHERE Staff.Id=99
It behaves normally.

Can anyone see what I'm doing wrong or is this a problem with Access?