Re: "At most one record can be returned by this subquery"

[Tom Ellison]

It is not strictly true that a subquery may return only one row or one
column. There are subqueries that return both multiple rows and/or multiple
columns.

A subquery used with IN may return only one column, but may return more than
one row.

A subquery used with EXISTS may return any number of rows or columns.

However, in the case of a subquery in certain contexts, it may return only
one row and only one column. This is definitely true for a subquery that
provides the value for a column in the SELECT clause, and for a subquery in
a WHERE clause using a simple comparison operator (equals, less than,
greater than, or a combination thereof).

The error message seen here is one that complains that a subquery is
returning more than one row in a context where one row is logically all
there can be. You can either change the subquery, probably by narrowing the
criteria for it or by using an aggregate, or you can change the context,
perhaps changing "=" to "IN".
--
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - your one stop database experts

By definition, a subquery must return only one value.

If you want multiple rows returned, just use a normal query with an inner
(or outer) join.

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

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

That query returns only one common case number ( I should
have several). Any ideas? Thanks!

-----Original Message-----
Add an ORDER BY clause to the subquery to it can figure out which row to
return, e.g.:
(SELECT TOP 1 [Incident Number]
FROM [arrests]
WHERE [LastName] = "RAY"
ORDER BY [Arrest Number])

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

Reply to the newsgroup. (Email address has spurious "_SpamTrap")

I'm getting this error message when I run a subquery: At
most one record can be return by this subquery.

I'm working with crime data. I want to find all the cases
(case numbers) associated with an offender and then I want
to find any other offenders associated with those same
cases. (bascialy, I'm looking for arrested associates).

I'm in Access 2000. My table is called arrest. I have
several fields in the table including first name, last
name, arrest number, incident number.

My query reads as follows (SELECT [Incident Number] FROM
[arrests] WHERE [LastName] = "RAY"). It is in the criteria
section of Incident Number.

"RAY" is the last name of the offender I'm searching on. I
know there are several cases where he and other people
were arrested (in these cases everyone would have a
different Arrest number, but the same Incident number). I
know such records exist because I have a small database
and I've entered all the data.

I would appreciate your help!
Thanks,
bbissell