‘At most one record can be returned by this subquery’ on SELECT TOP 1

Anyone who has had to write any Jet SQL for an Access database is probably well aquainted with frustration. The way it nests its joins is horrendous for one thing, but here’s a little trap that caught me up recently.

I was using a subquery, and attempting to ensure that it only returned one record (as required by a subquery within a select) by using the SELECT TOP 1 notation. I received the following error message:

At most one record can be returned by this subquery.

This seemed strange, as I had specifically told it to only return one record. It turns out that when Access sees two records that it considers ‘equivilent’ it will ignore the 1 that you placed after the TOP and return multiple records. There are various ways to try to overcome this. You could try to aggregate the data using GROUP BY to ensure that there is ano equivilence, but Allen Browne describes a very useful workaround to this behaviour on his Microsoft Access Tips for Serious Users. If you place the primary key of the table in the ORDER BY clause of the sub-query, then Access can determine an exact ordering, and therefore no two records will be considered equivilent. This works a treat.

Source Used

Advertisements

About Adam David Collings

Adam Collings is a writer of speculative fiction who works as a software engineer during the day. He lives in Tasmania, Australia with his wife and two children. Adam is currently working on a science fiction novel.
This entry was posted in Programming. Bookmark the permalink.

One Response to ‘At most one record can be returned by this subquery’ on SELECT TOP 1

  1. Paul Booth says:

    Adam,
    This is how I got to your site:
    SELECT [Eqpt List All ].*
    FROM [Eqpt List All ]
    WHERE [Eqpt List All ].[Unit ID]=
    (SELECT TblUnitIDRemoved.[Unit ID]
    FROM TblUnitIDRemoved);
    This query did not work; got At most one record can be returned by this subquery

    However this one did;
    SELECT [Eqpt List All ].*
    FROM [Eqpt List All ]
    WHERE [Eqpt List All ].[Unit ID] in
    (SELECT TblUnitIDRemoved.[Unit ID]
    FROM TblUnitIDRemoved);

    Its in the SQL Subqueries help section ….. I was lucky to make the = vs IN connection. Hope it helps somebody.

    Where in Tasmania do you live?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s