ms-access: runtime error 3354

Question

i'm having a problem running an sql in ms-access. im using this code:

SELECT readings_miu_id, ReadDate, ReadTime, RSSI, Firmware, Active, OriginCol, ColID, Ownage, SiteID, PremID, prem_group1, prem_group2  
INTO analyzedCopy2  
FROM analyzedCopy AS A 
WHERE ReadTime =  (SELECT TOP 1 analyzedCopy.ReadTime FROM analyzedCopy  WHERE analyzedCopy.readings_miu_id = A.readings_miu_id  AND analyzedCopy.ReadDate = A.ReadDate  ORDER BY analyzedCopy.readings_miu_id, analyzedCopy.ReadDate, analyzedCopy.ReadTime)
ORDER BY A.readings_miu_id, A.ReadDate ;

and before this i'm filling in the analyzedCopy table from other tables given certain criteria. for one set of criteria this code works just fine but for others it keeps giving me runtime error '3354'. the only diference i can see is that with the criteria that works, the table is around 4145 records long where as with the criteria that doesn't work the table that im using this code on is over 9000 records long. any suggestions?

is there any way to tell it to only pull half of the information and then run the same select string on the other half of the table im pulling from and add those results to the previous results from the first half?

The full text for run-time error '3354' is that it is "At most one record can be returned by this subquery."

I just tried to run this query on the first 4000 records and it failed again with the same error code so it can't be the ammount of records i would think.

Answer 1

I don't know if this would work or not (and I no longer have a copy of Access to test on), so I apologize up front if I'm way off.

First, just do a select on the primary key of analyzedCopy to get the mid-point ID. Something like:

SELECT TOP 4500 readings_miu_id FROM analyzedCopy ORDER BY readings_miu_id, ReadDate;

Then, when you have the mid-point ID, you can add that to the WHERE statement of your original statement:

SELECT ...
INTO ...
FROM ...
WHERE ... AND (readings_miu_id <= {ID from above}
ORDER BY ...

Then SELECT the other half:

SELECT ...
INTO ...
FROM ...
WHERE ... AND (readings_miu_id > {ID from above}
ORDER BY ...

Again, sorry if I'm way off.

Answer 2

See this:

http://allenbrowne.com/subquery-02.html#AtMostOneRecord

What is happening is your subquery is returning two identical records (based on the ORDER BY) and the TOP 1 actually returns two records (yes that's how access does the TOP statement). You need to add fields to the ORDER BY to make it unique - preferable an unique ID (you do have an unique PK don't you?)

As Andomar below stated DISTINCT TOP 1 will work as well.

Answer 3

What does MS-ACCESS return when you run the subquery?

SELECT TOP 1 analyzedCopy.ReadTime 
FROM analyzedCopy
WHERE analyzedCopy.readings_miu_id = A.readings_miu_id  
AND analyzedCopy.ReadDate = A.ReadDate  
ORDER BY analyzedCopy.readings_miu_id, analyzedCopy.ReadDate, 
         analyzedCopy.ReadTime

If it returns multiple rows, maybe it can be fixed with DISTINCT:

SELECT DISTINCT TOP 1 analyzedCopy.ReadTime 
FROM ... rest of query ...