At most one record can be returned ERROR (MS ACCESS)

Question

Hi All,

I understand WHY I am getting the error, but need a work around to enable the inner nested subquery to pass two parameters up to the first (main) query.

Following other advice I tried ordering the passed values and a few other wild ideas but no luck.

IS it possible to create a NEW table on the fly which is poulated by the output of the subquery and use a seperate (not nested) query to use that data to find the community matron?

I can supply tables but they are very simple and ALL fields are text at present, this is a trial prototype to get me back in the frame!

SELECT communitymatron.familyname
FROM communitymatron
WHERE communitymatron.cmid=
  (SELECT cmid
   FROM  cmsu
  WHERE cmsu.suid=
    (SELECT suid
    FROM surgery
    WHERE surgery.surgeryname=name))
    ORDER BY surgeryname;

Best wishes,

Jim

---

Best wishes, Jim Jim Barr Best is the enemy of good enough Barrs law of recurcive futility:- If you're smart enough to use one of these..... .....you can probably manage without one

Answer 1

Hello Jim,

Just usw In operator instead of equal compare to the Sub query.

SELECT communitymatron.familyname
 FROM communitymatron
 WHERE communitymatron.cmid IN 
   (SELECT cmid
    FROM  cmsu 
  WHERE cmsu.suid IN
     (SELECT suid 
    FROM surgery
     WHERE surgery.surgeryname=name))
     ORDER BY surgeryname;

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Thanks OLAF,

I had tried IN, ( someone suggested it), but obviosly got the syntax wrong.

---

Best wishes, Jim Jim Barr Best is the enemy of good enough Barrs law of recurcive futility:- If you're smart enough to use one of these..... .....you can probably manage without one

Answer 3

Hi Jim,

I did a similar test. Please see the following three tables:
Table comunitymatron:
Cmid    surgeryname     familyname
10         first                     family1
20         second               family2
30         third                   family3
Table cmsu:
Cmid    suid
10         1
20         2
30         3
20         4
30         5
Table surgery:
Sudi    surgeryname
1          first
2          second
3          third
4          first
5          first

When I used the equal operator in your query, it did return the error:

But when I used the IN operator as Olaf suggested, it could be run successfully:

 
Base on your update, you got the syntax wrong. Could you please post the specific error? And also please supply those three tables if I’m wrong, thanks.

Best Regards,
Ray Chen