Ms-access: runtime error 3354
I have a problem with 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 that I fill the parsed Copy table from other tables with certain criteria. for one set of criteria this code works very well, but for others it keeps giving me a runtime error "3354". the only difference I can see is that with the criteria that works, the table is about 4145 records, where, as with the criteria that don't work, the table it uses this code in is over 9000 records. any suggestions?
is there a way to tell it to only pull half of the information and then run the same select row in the other half of the table im pulled and add those results to the previous results from the first half?
The full text for runtime error "3354" is that "At most one record can be returned in this subquery."
I just tried to run this query on the first 4000 records and it worked again with the same error code, so it may not be an array of records I might think.
a source to share
See this:
http://allenbrowne.com/subquery-02.html#AtMostOneRecord
What happens is your subquery returns two identical records (based on ORDER BY) and TOP 1 actually returns two records (yes, like accessing the TOP operator). You need to add fields to the ORDER BY to make it unique - your preferred unique ID (you have a unique PK, don't you?)
As pointed out by Andomar below, DISTINCT TOP 1 will also work.
a source to share
What does MS-ACCESS return when running a 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 lines, perhaps it can be fixed with DISTINCT:
SELECT DISTINCT TOP 1 analyzedCopy.ReadTime
FROM ... rest of query ...
a source to share
I don't know if this will work or not (and I no longer have a copy of Access to test), so I apologize if I leave.
First, just select the primary key to parseCopy to get the midpoint id. Sort of:
SELECT TOP 4500 readings_miu_id FROM analyzedCopy ORDER BY readings_miu_id, ReadDate;
Then, when you have the midpoint id, you can add this to the WHERE clause 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 leave.
a source to share