I have a query which when run gives the error "at most one record can be returned by this subquery" but when I run a report on the same query the report loads perfectly. This is the code for the query the query starts to load but then gives the error.
SELECT R1.Horse, R1.Racedate, R1.racedate-(SELECT racedate
FROM starts2023 AS R2
WHERE R2.horse = R1.horse
AND racedate =
(SELECT MAX(racedate)
FROM starts2023 AS R3
WHERE R3.horse = R1.horse
AND R3.racedate < R1.racedate)) AS DifferenceInDays
FROM starts2023 AS R1
WHERE (((R1.Horse) Is Not Null))
ORDER BY R1.Horse, R1.Racedate;
Any help so that the query can run OK?
SELECT R1.Horse, R1.Racedate, R1.racedate-(SELECT racedate
FROM starts2023 AS R2
WHERE R2.horse = R1.horse
AND racedate =
(SELECT MAX(racedate)
FROM starts2023 AS R3
WHERE R3.horse = R1.horse
AND R3.racedate < R1.racedate)) AS DifferenceInDays
FROM starts2023 AS R1
WHERE (((R1.Horse) Is Not Null))
ORDER BY R1.Horse, R1.Racedate;
Any help so that the query can run OK?