Solved Query returning "at most one record can be returned" (1 Viewer)

[Kolinu]

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?

 

[GPGeorge]

You have a subquery nested inside another subquery:

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)

If you run this as a standalone query, how many instances of racedate are returned?

 

[jdraw]

Not an answer - but the subject of your post reminds me of older responses to a poster with similar subject. (2015)

 

[GPGeorge]

You have a subquery nested inside another subquery:

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)

If you run this as a standalone query, how many instances of racedate are returned?

Note: This will not run as-is because of the aliased table, but with proper modification, what does it return?

 

[theDBguy]

You could also try using DLookup(). The speed should be close to the original, since you're using a correlated subquery anyway. Just a thought...

 

[plog]

You need to fix Query 2 such that it returns at most 1 record. Each SELECT is a query:

Query 1 starts here -> SELECT R1.Horse, R1.Racedate, R1.racedate - (
Query 2 starts here -> SELECT racedate FROM starts2023 AS R2 WHERE R2.horse = R1.horse AND racedate = (
Query 3 starts here -> SELECT MAX(racedate) ...

Query 1 is the main query and can return multiple records without error so it is not the issue.
Query 3 is used as criteria in Query 2 and it must return at most 1 field in 1 record. But because it uses MAX() in the SELECT and no GROUP BY it is ensured to do that so it is not the issue.
Query 2 is used as a calculation in Query 1 so it must return at most 1 field in 1 record so Query 1 can do its calculation. But the way it is built it does not ensure it does. If the last day that horse raced it raced multiple times it returns all those records and Query 1 doesn't know how to handle that and throws the error.

My guess is throwing just about any aggregate function around racedate in Query 2 will fix it. Just for consistency I'd go with MAX, but MIN, AVG, FIRST and LAST would all work. Using DISTINCT and GROUP BY in Query 2 would also work. Go with MAX() though.

 

[Kolinu]

Worked perfectly, thank for the help Plog.