Subquery can only return one record when used inside the WHERE cla

[Guest]

My nested query is giving me the Access 2003 error, "At most one record can
be returned by this subquery." So obviously I didn't choose the right kind
of nested subquery because I need to be able to return more than one record.
The purpose of the query is to find employees on crew 1 who are certified and
assigned to the Trim post from the set of employees who were not absent on
11/2/07. Because more than one employee might have been absent on 11/2/07, I
need to return more than one record. The SQL statement is as follows:

SELECT EEName

FROM (
SELECT DISTINCT Employees.[Employee Name] AS EEName,
EmployeeCertifications.SS AS EESS

FROM Assignments INNER JOIN (EmployeeCertifications INNER JOIN Employees ON
EmployeeCertifications.SS = Employees.SS) ON Assignments.AssignedEmployeeSS =
EmployeeCertifications.SS

WHERE (((Assignments.AssignmentStartDate)<= #11/2/2007#) AND
((Employees.EndDate) Is Null) AND ((EmployeeCertifications.Certification)=
'Trim') AND ((Assignments.AssignmentEndDate) Is Null) AND ((Employees.Crew)=
1) AND ((Assignments.Post)= 'Trim')) )

WHERE (

SELECT AbsencesTardies.SS AS AbsentEESS

FROM AbsencesTardies INNER JOIN Employees ON AbsencesTardies.SS =
Employees.SS

WHERE (((AbsencesTardies.[Date of Occurrence])= #11/2/2007#) AND
((AbsencesTardies.[Absent Hrs])>=8)) )<> [EESS]

 

[Guest]

Why don't you start with separate queries which you can run independently.
Then when you have debugged both queries, you can combine them.

-Dorian

 

[Michel Walsh]

Indeed, your WHERE clause is like:

WHERE (SELECT ... ) <> EESS


If the sub-query return multiple values, that could be, pseudo code, like:

WHERE {1 , 3, 11, 22 } <> EESS


That is mathematically not 'evident' what you meant by such thing. You
probably want that EESS, one value, be different of ANY values of the
sub-query, so, try:


WHERE EESS <> ALL ( SELECT ... )


Yes, EESS value has to be different of each and every values returned by the
sub-query, or, more standard


WHERE EESS NOT IN( SELECT ... )


If you use = or <> or > or < or... similar operator, the sub query has to
return just one record, and just one value. To be sure it returns just one
record, use an aggregate and no group:


WHERE scalarField > (SELECT MAX(something) FROM somewhere )



( a TOP 1 is a poor solution, since more than one record can be returned
with a top 1 on and ORDER allowing ex-equo).



Anyhow, your case seems to be a NOT IN, or <> ALL, rather than this second
kind of possibilities, so try a NOT IN.



Hoping it may help,
Vanderghast, Access MVP