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

by Adam on July 23rd, 2010

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