access subquery error: "not enough storage is available to complete
this operation"
Query---
SELECT
TOP 100 PERCENT
UPPER(dbo.Employee.last + N', ' + dbo.Employee.first) AS Employee,
Employee.employeeNumber
FROM
(SELECT DISTINCT
TOP 100 PERCENT
Employee.employeeNumber
FROM
Courses INNER JOIN
Inservices ON dbo.Courses.courseNumber = Inservices.courseNumber INNER
JOIN
Register ON Inservices.courseCounter = Register.courseCounter INNER
JOIN
Employee ON Register.employeeNumber = Employee.employeeNumber
WHERE
(Courses.courseDescription = N'yyyyy')
AND
(Employee.terminationDate IS NULL)
AND
(Register.attendedWorkshop = N'yes')
) AttendedTraining
RIGHT OUTER JOIN Employee ON AttendedTraining.employeeNumber =
Employee.employeeNumber
WHERE
(AttendedTraining.employeeNumber IS NULL)
AND
(Employee.terminationDate IS NULL)
AND
(NOT (Employee.employeeNumber IN (0, 1)
)
)
ORDER BY Employee.last + N', ' + dbo.Employee.first
When I try to add AttendedTraining.employeeNumber I get the error
noted. I can add Employee.employeeNumber (or any other column for that
matter) from the real table.
Any thoughts? The query acts normally when not nested, i.e. the
AttendedTraining base query works normally. Is this just a limitation
with access or what? Thanks!
Jeff
