using SUBQUERY giving error: "YOU HAVE WRITTEN A SUBQUERY ..."

[cuok]

Hi friends!
I need to use a query (bellow and bolded) as subQuery but i dont know how!
i'm using a query named 'DbMax' and this shell be one of its columns.
'DbMax' has one table with same PK (id) named Tbl_Patients.

EXAMPLE of another column in DbMax that works fine:

LastBmiSds: (select last(bmisds) from TblOrganscreening where id = Tbl_Patients.id and TblOrganscreening.VISITCLINICDATE<=date() )

Here the query (works fine when running it as query):

Clubbing: (SELECT TblOrganScreening.Id, TblOrganScreening.Clubbing, TblOrganScreening.VisitClinicDate
FROM TblOrganScreening
WHERE (((TblOrganScreening.VisitClinicDate) In (SELECT MAX(VisitClinicDate)
FROM TblOrganScreening
GROUP BY ID)))
ORDER BY TblOrganScreening.Id)


WHEN RUNNING 'DbMax' i get this error message:
&quot;YOU HAVE WRITTEN A SUBQUERY THAT CAN RETURN MORE THAN ONE FIELD WITHOUT USING THE exists RESERVED WORD IN THE MAIN QUERY’S from CLAUSE. REVISE THE select STATEMENT OF THE SUBQUERY TO REQUEST ONLY ONE FIELD &quot;

Thanks for any help or idea
CUOK

 

[Golom]

Since the query that you have supplied runs fine (I've checked ... it does.) ... can we see the query in which this is a sub-query?

The message is quite correct. This query does return three fields in each record so if you are using it in something like

... Field IN (<SubQuery>) ...

then you will get this message because there are three fields in every record that you are trying to compare with one field in the main query.

 

[cuok]

Good morning Golom and All!

Thank u Golom.
(I slept till now , it was about 00:30 when i wrote my post)

Here the query in which the sub-query has to be:

SELECT Tbl_Patients.Id, IIf((select max(Temperature) from TblOrganscreening where id = Tbl_Patients.id >38),&quot;BIG&quot;,&quot;SMALL&quot AS MaxTemperature, (select max(Na) from Tbl_Biochemistry where id = Tbl_Patients.id ) AS MaxNa, IIf((select max(Amiloride) from Tbl_Npd where id = Tbl_Patients.id=&quot;&quot,&quot;ND&quot;,&quot;NO5&quot AS MaxAmiloride, (select max(BaseLine) from Tbl_Npd where id = Tbl_Patients.id ) AS MaxBaseLine
FROM Tbl_Patients
GROUP BY Tbl_Patients.Id;

I'm still keeping to try...
THANX AGAIN
CUOK

 

[Golom]

What do you expect from

where id = Tbl_Patients.id >38 ??

This is going to evaluate to

where id = -1
OR
where id = 0

I suspect that you need something more like

where id = Tbl_Patients.id AND Tbl_Patients > 38

Always check your WHERE clause to see if it makes sense in the real world.