Correlated Subqueries

[Springy]

Right, this should be simple but I can't seem to get it working.

I have two tables, table 1 is a list of things ran by a scheduler and
table 2 is a history of when the schedule in table 1 was ran
(history). The history table has a column of when the next schedule
should be ran. So in order to produce a list of schedules and their
net run date I issue the following query to access 2000.

SELECT S.*, SH.*
FROM (LASPortal_Schedule AS S LEFT OUTER JOIN
LASPortal_ScheduleHistory AS SH ON S.ScheduleID =
SH.ScheduleID)
WHERE ((SH.ScheduleHistoryID =
(SELECT Top 1 S1.ScheduleHistoryID
FROM LASPortal_ScheduleHistory AS S1
WHERE (S1.ScheduleID = S.ScheduleID)
ORDER BY S1.NextStart DESC)) OR
SH.ScheduleHistoryID IS NULL)

I get the error "at most one record can be retrieved from this query".
If I alter the query slightly I get a prompt for the S.ScheduleID in
the sub query (I assume it's this column, see the following), and if I
replace S.ScheduleID in the sub query with a constant it works (e.g.
240).

Your input, many thanks

 

[John Spencer (MVP)]

Since TOP 1 (in Access) can return ties (even though in this instance it
probably won't) Access will complain. Can you work around the problem by using
MAX or Min OR using IN instead of equals if you are sure that Top 1 will only
return one value.

SELECT ...
WHERE SH.ScheduleHistoryId IN
(SELECT TOP 1 S1.ScheduleID ...