Cursor Simulation SQL Failure

[Guest]

I created some SQL which I incorporated into a query to select a current and
prior record. After a few second delay, after clicking the button to go to
the end of the query results, the following message is displayed: At most
one record can be returned by this subquery. I put distincts in each subquery
but got the same results. Other than that, it works and returns the current
record as well as the previous record in the PrevRec field. Any ideas on how
to fix this issue ?

SELECT A.HRNum, A.FullName, A.Code, A.HireDate, (Select Distinct B.Fullname
From tblEmp1 B Where A.Code = B.Code And B.HireDate =
( Select Distinct max(C.HireDate) From tblEmp1 C Where B.Code = C.Code And
C.HireDate < A.HireDate)) AS PrevRec
FROM tblEmp1 AS A
ORDER BY A.HireDate;

 

[Sylvain Lafontaine]

Try adding a Top 1 clause in the subquery.