SubQuery - "at most one record..."

[dchendrickson]

I am running Access2002/XP Pro and programming primarily
using ADO structurs.

I have a table with three fields that hold information
relating to circuit connections on a PC Card. All three
fields comprise the primary key of the table. The fields
are CardVersion, PinID, MateID.

CardVersion - holds an integer specifying which PC Card
version the record is associated with.

PinID - holds a unique Long from another table that
specifies which Pin on this version the record is
associated with.

MateID - holds a unique Long from the same table as above
and it specifies what the PinID is connected to.

So each record specifies a circuit from point A (PinID)
to point B (MateID) on this version of the PC Card.

A particular Pin may be connected to more than one mate
(a splice). So there may be multiple records with the
same CardVersion and PinID information. Hence, the need
for all three fields as the primary key.

Now to the query problem.

I have an SQL statement to open a recordset that is to
return a list of Labels of all the Mates for a particular
PinID. The SQL is:

SELECT PinLabel FROM tblPin WHERE PinID = (SELECT MateID
FROM tblVersion WHERE CardVersion = 1 AND PinID = 123;

The actual SQL is messier, but I have tried to clarify.

When my code tries to open the recordset based on this
SQL, it returns the error message:

Error Number: -2147467259 - At most one record can be
returned by this subquery.

Are subqueries only designed to return 1 record? I
suspect there is some SQL Keyword I am missing that will
do the trick, but this is where I need your guidence.

Thanks for your help.

-dc

 

[Chris]

You say that you have three fields as the primary key, yet
you only specify two in the sub query.


try IN instead of = ...

 

[Allen Browne]

A subquery in the FROM clause can return no more than one record.

If you want to return all the matches, just drag the table into the query,
and use a JOIN. (You may need an outer join.)

If you want to restrict the subquery to just one record, use an aggregator
such as Max(), or a predicate such as TOP 1. In case there are two identical
values, you have to give Access some way to decide which one to return, and
a simple solution is to add the primary key to the ORDER BY clause of the
subquery.

 

[Guest]

Allen,

As usual, you come to the rescue! After your suggestion,
dragging the other table into the query seemed so
obvious. Makes me wonder where my mind is sometimes....

The SQL is now working just as I had wished. Thanks again.

-dc