Circumvent "At most one record can be returned by this subquery"error

[nik]

Hi,

I have a nested query, in which the subquery can return more than one
record, but this is not allowed by Access. I've found various posts
where people have gotten the error, "At most one record can be
returned by this subquery" and really didn't want multiple records
returned, but I do. How can one circumvent this error in Access and
have a query rely upon a subquery that returns multiple records?

Thanks,
Nik

 

[Jerry Whittle]

You might get it to work with either an In/Not In or Exists/Not Exists
clauses. It would look something like below.

SELECT *
FROM ASA
WHERE Not Exists
(SELECT * FROM ASA_CHILD
WHERE ASA.AsaID = ASA_Child.AN_id);

 

[nik]

You might get it to work with either an In/Not In or Exists/Not Exists
clauses. It would look something like below.

SELECT *
FROM ASA
WHERE Not Exists
  (SELECT *  FROM ASA_CHILD
   WHERE ASA.AsaID =  ASA_Child.AN_id);

Hi,

I actually got this to work, but simply dividing the query in two.

Originally I had this nested query:
SELECT File_Name
FROM tblFiles
WHERE tblFiles.ID = (SELECT tblFileLine.File_ID FROM tblFileLine
WHERE tblFileLine.Line_ID = Forms!frmSearch!Line );

Which I divided into qryPart1:
SELECT File_ID
FROM tblFileLine
WHERE [tblFileLine].Line_ID=Forms![frmSearch]!Line;

and qryPart2:

SELECT [tblFiles].File_Name
FROM tblFiles, qryPart1
WHERE [tblFiles].ID=[qryPart1].File_ID;

Thanks for the help,
Nik

 

[John W. Vinson]

Hi,

I have a nested query, in which the subquery can return more than one
record, but this is not allowed by Access. I've found various posts
where people have gotten the error, "At most one record can be
returned by this subquery" and really didn't want multiple records
returned, but I do. How can one circumvent this error in Access and
have a query rely upon a subquery that returns multiple records?

Thanks,
Nik

By correcting the structure of the query... which we cannot see. Care to post
it?

My guess is that you'll want to use the syntax

IN (SELECT... )

rather than

=(SELECT... )

but it's impossible to be specific given your post.

 

[Allen Browne]

Good: you got it sorted, Nik.

For future reference, there are 4 suggestions for handling that issue here:
http://allenbrowne.com/subquery-02.html#AtMostOneRecord

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

You might get it to work with either an In/Not In or Exists/Not Exists
clauses. It would look something like below.

SELECT *
FROM ASA
WHERE Not Exists
(SELECT * FROM ASA_CHILD
WHERE ASA.AsaID = ASA_Child.AN_id);

Hi,

I actually got this to work, but simply dividing the query in two.

Originally I had this nested query:
SELECT File_Name
FROM tblFiles
WHERE tblFiles.ID = (SELECT tblFileLine.File_ID FROM tblFileLine
WHERE tblFileLine.Line_ID = Forms!frmSearch!Line );

Which I divided into qryPart1:
SELECT File_ID
FROM tblFileLine
WHERE [tblFileLine].Line_ID=Forms![frmSearch]!Line;

and qryPart2:

SELECT [tblFiles].File_Name
FROM tblFiles, qryPart1
WHERE [tblFiles].ID=[qryPart1].File_ID;

Thanks for the help,
Nik