spluper
asked on
Subquery - Gives Error "At most one record can be returned by this subquery"
I don't understand why I get this message ("At most one record can be returned by this subquery") when I run the following query with the subquery.
SELECT T.ID, (SELECT s.DOC_ID FROM tblSYS_REF AS s, tblSYS AS t WHERE ((([t].[ID])=[s].[SYS_ID])
FROM tblSYS AS T;
I can extract the subquery and run it on its own as a regular select query and it returns multiple records but not when it is run as subquery.
SELECT T.ID, (SELECT s.DOC_ID FROM tblSYS_REF AS s, tblSYS AS t WHERE ((([t].[ID])=[s].[SYS_ID])
FROM tblSYS AS T;
I can extract the subquery and run it on its own as a regular select query and it returns multiple records but not when it is run as subquery.
you need a key to join on - you can't select the results of one query as a field.
ASKER
All of the fields labeled ID are keys, and fields ???_ID are the foriegn keys in the related table. The relationships are defined 1 to many in the relationships window. I have tried it also using joins but I get the same result.
The sql statement and subquery with joins (sans aliases) looks like: SELECT tblSYS.ID, (SELECT tblSYS_REF.DOC_ID
FROM tblSYS INNER JOIN tblSYS_REF ON tblSYS.ID = tblSYS_REF.SYS_ID
WHERE (((tblSYS_REF.RefMap)='PID
FROM tblSYS;
The sql statement and subquery with joins (sans aliases) looks like: SELECT tblSYS.ID, (SELECT tblSYS_REF.DOC_ID
FROM tblSYS INNER JOIN tblSYS_REF ON tblSYS.ID = tblSYS_REF.SYS_ID
WHERE (((tblSYS_REF.RefMap)='PID
FROM tblSYS;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok...that is what I needed to hear. If there was a way to split points between the bob_online and funke I would.
Actually, FYI, the original sql query turned out to work, just fine....and for all 1000 or so IDs in tblSYS it returns only one record for each ID in tblSYS except for, as you pointed out, one of the ID numbers in tblSYS that returned two records and that was what was causing the problem.
So really the problem was with the data not so much with the query which was what I was focusing on. I fixed the data by deleting the duplicate a defining a second unique index to prevent the duplicate data from happening in the future......thanks a million to the both of you...
Actually, FYI, the original sql query turned out to work, just fine....and for all 1000 or so IDs in tblSYS it returns only one record for each ID in tblSYS except for, as you pointed out, one of the ID numbers in tblSYS that returned two records and that was what was causing the problem.
So really the problem was with the data not so much with the query which was what I was focusing on. I fixed the data by deleting the duplicate a defining a second unique index to prevent the duplicate data from happening in the future......thanks a million to the both of you...
Data problem or not, I would still question whether that is an efficient way to set up a query...
With the Select as a field, you are executing the query on the tblsys_ref table once for each record in tblsys rather than letting access join the two tables based on the key relationships which is what it is Optimized to do.
I would still rewrite the query...
With the Select as a field, you are executing the query on the tblsys_ref table once for each record in tblsys rather than letting access join the two tables based on the key relationships which is what it is Optimized to do.
I would still rewrite the query...