At most one record can be returned...

[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

 

[Stefan Hoffmann]

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?

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

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

mfG
--> stefan <--

 

[Chad]

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

 

[Stefan Hoffmann]

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

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 <--

 

[Chad]

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

 

[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.

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

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 Hoffmann]

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?

Yes, your outer FROM creates a cartesian product (cross join) of three
tables. This may influence your subquery.


mfG
--> stefan <--

 

[Chad]

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