At most one record can be returned...

  • Thread starter Thread starter Chad
  • Start date Start date
C

Chad

Hello everyone. I am trying to run a query with an imbedded subquery but
continue to get the error "At most one record can be returned from this
subquery."
I have checked and rechecked that the subquery returns only one record based
on the criteria specified. Does anyone have any idea as to why this query
continues to return that error message?

************************************************************
SELECT tblYears.Year_ID, tblMonths.Month_ID, tblResidence.Residence_ID,

(SELECT Temp3.Days FROM qryServiceNightsbyMo As Temp3 WHERE
Temp3.Residence_ID = tblResidence.Residence_ID AND Temp3.Period_ID = Month_ID
AND Temp3.Annual_ID=Year_ID ) AS ServiceDays

FROM tblMonths, tblYears, tblResidence

WHERE (((tblResidence.Residence_History_Code_ID)=461) AND
((tblResidence.From_Date)<=DateSerial([Year_ID],[Month_ID]+1,0)) AND
((tblResidence.To_Date)>=DateSerial([Year_ID],[Month_ID]+1,0)))
GROUP BY tblYears.Year_ID, tblMonths.Month_ID, tblResidence.Residence_ID;

************************************************************

Best,
Chad
 
hi Chad,
I have checked and rechecked that the subquery returns only one record based
on the criteria specified. Does anyone have any idea as to why this query
continues to return that error message?
Click to expand...
A subquery used to return a field value must return only one record.
SELECT tblYears.Year_ID, tblMonths.Month_ID, tblResidence.Residence_ID,

(SELECT Temp3.Days FROM qryServiceNightsbyMo As Temp3 WHERE
Temp3.Residence_ID = tblResidence.Residence_ID AND Temp3.Period_ID = Month_ID
AND Temp3.Annual_ID=Year_ID ) AS ServiceDays
Click to expand...
This subquery can return more then one. Check it with

(SELECT Count(*) FROM qryServiceNightsbyMo As Temp3 WHERE
Temp3.Residence_ID = tblResidence.Residence_ID AND Temp3.Period_ID =
Month_ID
AND Temp3.Annual_ID=Year_ID ) AS ServiceDays
FROM tblMonths, tblYears, tblResidence
Click to expand...


mfG
--> stefan <--
 
Stefan

Thank you for the response. Unfortunately that did not return the correct
data. Let me go through some of the solutions I have tried so far as it
might help others get a better understanding of my problem:

1. I tried to return MIN(Temp3.Days) and MAX(Temp3.Days) from the subquery
a. This returns records that do not match the criteria set forth
in the where clause

2. I tried to return SUM(Temp3.Days )
a. This returns records that do not match the criteria set forth
in the where clause

3. Using the COUNT(Temp3.Days )
a. This returns the number of records but also does not match
the criteria set forth in the where clause

These things being said, it look s as if Access is recognizing more than one
record at a time being associated with the subquery, but like I said, I have
checked and rechecked and based on the logic, that doesn't seem to be the
case.

If anyone else has any suggestions, please let me know.

Thanks again,
Chad
 
hi Chad,
3. Using the COUNT(Temp3.Days )
a. This returns the number of records but also does not match
the criteria set forth in the where clause
Click to expand...
What does Count(*) return?

btw, use more aliases:

SELECT
Y.Year_ID,
M.Month_ID,
R.Residence_ID,
(SELECT Count(*)
FROM qryServiceNightsbyMo As T
WHERE T.Residence_ID = R.Residence_ID
AND T.Period_ID = M.Month_ID
AND T.Annual_ID = Y.Year_ID
) AS ServiceDays
FROM
tblMonths M,
tblYears Y,
tblResidence R
WHERE R.Residence_History_Code_ID=461)
AND R.From_Date <= DateSerial([Year_ID], [Month_ID]+1, 0)
AND R.To_Date >= DateSerial([Year_ID], [Month_ID]+1, 0)
GROUP BY Y.Year_ID, M.Month_ID, R.Residence_ID;

Why do you need this cross-join?

mfG
--> stefan <--
 
Stefan,

Sorry, I misunderstood your response. The count function does, in fact,
show that the subquery returns multiple record, however, my question pertains
to why. When I manually go through, there is only one record in the
qryServiceNightsbyMo for each Year_ID, Month_ID, and Residence_ID. Any ideas
or suggestions?

Chad
 
The cross join is needed becuase I only want to match the 3 fields in the
subquery (Annual_ID, Month_ID, and Residence_ID) to the three fields in the
parent query (Year_ID, Month_ID, Residence_ID). Otherwise, there should be
more than one result per record in the subquery.

Stefan Hoffmann said:
hi Chad,
3. Using the COUNT(Temp3.Days )
a. This returns the number of records but also does not match
the criteria set forth in the where clause
Click to expand...
What does Count(*) return?

btw, use more aliases:

SELECT
Y.Year_ID,
M.Month_ID,
R.Residence_ID,
(SELECT Count(*)
FROM qryServiceNightsbyMo As T
WHERE T.Residence_ID = R.Residence_ID
AND T.Period_ID = M.Month_ID
AND T.Annual_ID = Y.Year_ID
) AS ServiceDays
FROM
tblMonths M,
tblYears Y,
tblResidence R
WHERE R.Residence_History_Code_ID=461)
AND R.From_Date <= DateSerial([Year_ID], [Month_ID]+1, 0)
AND R.To_Date >= DateSerial([Year_ID], [Month_ID]+1, 0)
GROUP BY Y.Year_ID, M.Month_ID, R.Residence_ID;

Why do you need this cross-join?

mfG
--> stefan <--
Click to expand...
 
hi Chad,
Sorry, I misunderstood your response. The count function does, in fact,
show that the subquery returns multiple record, however, my question pertains
to why. When I manually go through, there is only one record in the
qryServiceNightsbyMo for each Year_ID, Month_ID, and Residence_ID. Any ideas
or suggestions?
Click to expand...
Yes, your outer FROM creates a cartesian product (cross join) of three
tables. This may influence your subquery.


mfG
--> stefan <--
 
Gotcha. The reason I need the cross join is because the fields To_Date and
From_Date in tblReservations are date ranges and I would like to return a
separate record for each month whose last day falls in that range for each
property.

Is there a better way to go about this than setting up the cartesian join?

Thanks again,
Chad
 
Back
Top