At Most one record can be returned by this subquery PROBLEM...

[JKDeveloper0718]

I have a report that is giving me this error which is comming from a query as follows:

At Most one record can be returned by this subquery

I have the SQL that causes it and its a very complicated statement that I did not write but am expected to fix here it is:

SELECT a.UnitStatusHistoryId, a.UnitApartmentId, a.UnitStatusId, ua.PHADevelopmentId, ua.PDCode, CInt(ua.UAUnitNumber) AS UAUnitNumber, ua.UMCode, ua.Address, ua.UnitAptBeds, ua.PHACensusTractId, ua.PHABedroomTypeId, a.MoveOut, (SELECT TOP 1 b.UnitStatusDate FROM tblUnitStatusHistory b WHERE (b.UnitStatusId = 3) AND (b.UnitApartmentId = a.UnitApartmentId) AND (b.UnitStatusHistoryId > a.UnitStatusHistoryId) AND (b.UnitStatusDate >= a.MoveOut) AND ((b.UnitStatusDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY b.UnitStatusHistoryId) AS MOInspection, (SELECT TOP 1 w.StartDate FROM tblWOUnitPrepDates AS w WHERE w.UnitApartmentId = a.UnitApartmentId
AND (w.StartDate >= a.MoveOut) AND ((w.ReadyDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY w.StartDate) AS StartDate, (SELECT TOP 1 w.TargetDate FROM tblWOUnitPrepDates AS w WHERE w.UnitApartmentId = a.UnitApartmentId
AND (w.StartDate >= a.MoveOut) AND ((w.ReadyDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY w.StartDate) AS TargetDate, (SELECT TOP 1 b.UnitStatusDate FROM tblUnitStatusHistory b WHERE (b.UnitStatusId = 4) AND (b.UnitApartmentId = a.UnitApartmentId) AND (b.UnitStatusHistoryId > a.UnitStatusHistoryId) AND (b.UnitStatusDate >= a.MoveOut) AND ((b.UnitStatusDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY b.UnitStatusHistoryId) AS VacantReady, (SELECT TOP 1 b.UnitStatusDate FROM tblUnitStatusHistory b WHERE (b.UnitStatusId = 5) AND (b.UnitApartmentId = a.UnitApartmentId) AND (b.UnitStatusHistoryId > a.UnitStatusHistoryId) AND (b.UnitStatusDate >= a.MoveOut) AND ((b.UnitStatusDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY b.UnitStatusHistoryId) AS Offered, (SELECT TOP 1 b.UnitStatusDate FROM tblUnitStatusHistory b WHERE (b.UnitStatusId = 7) AND (b.UnitApartmentId = a.UnitApartmentId) AND (b.UnitStatusHistoryId > a.UnitStatusHistoryId) AND (b.UnitStatusDate >= a.MoveOut) AND ((b.UnitStatusDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY b.UnitStatusHistoryId) AS Refused, (SELECT TOP 1 b.UnitStatusDate FROM tblUnitStatusHistory b WHERE (b.UnitStatusId = 6) AND (b.UnitApartmentId = a.UnitApartmentId) AND (b.UnitStatusHistoryId > a.UnitStatusHistoryId) AND (b.UnitStatusDate >= a.MoveOut) AND ((b.UnitStatusDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY b.UnitStatusHistoryId) AS Accepted, a.Leased, ua.Notes, IIf(IsNull(a.UnitApartmentId),"",(s.[1] & " " & s.[2] & " " & s.[3] & " " & s.[4] & " " & s.[5] & " " & s.[6] & " " & s.[7] & " " & s.[8] & " " & s.[9] & " " & s.[10])) AS AccessDesc
FROM (qryMoveOutMoveIn AS a INNER JOIN qryUnitAddress AS ua ON a.UnitApartmentId = ua.UnitApartmentId) LEFT JOIN qryUnitAccessibilityDetailsCrosstab AS s ON a.UnitApartmentId = s.UnitApartmentId
WHERE (ua.UAStatusTypeId <> 4)
AND ( (a.MoveOut<=GetEndDate()) AND ((a.Leased>=GetStartDate()) OR (IsNull(a.Leased))))
ORDER BY a.UnitApartmentId, a.UnitStatusHistoryId DESC;


I know its crazy but Any suggestion on how I should trouble shoot this query would be great.

Thanks.

 

[dhookom]

I expect that you have at least one subquery that returns more than a single record. You may have "ties" causing "SELECT TOP 1" to actually return more than one record. This can be avoided by adding the primary key field to your ORDER BY. For instance:

(SELECT TOP 1 w.StartDate FROM tblWOUnitPrepDates AS w WHERE w.UnitApartmentId = a.UnitApartmentId AND (w.StartDate >= a.MoveOut) AND ((w.ReadyDate <= a.Leased) OR (IsNull(a.Leased) = -1)) ORDER BY w.StartDate, w.PKField) AS StartDate,

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]