"at most one record can be returned by subquery" after appending new rows

[jasperk]

Hi,



I have a subquery on a table which got the previous and next rows from a table. This worked fine, until I started replacing my table with real data instead of dummy-data. I now receive the "at most one record can be returned by subquery" error every time I run it. I checked that I do not have any identical rows.

My query:

SELECT z.DateFrom, z.DateTo, z.ArticleID, z.retailerID, z.Actuals, z.Statistical, (SELECT y.Actuals FROM tblActuals AS y WHERE (y.retailerID=z.retailerID) AND (y.ArticleID=z.ArticleID) AND ((DatePart("ww",z.DateFrom,2,3)-1)=DatePart("ww",y.DateFrom,2,3))) AS ActualsMin1 FROM tblActuals AS z;

I append new rows via a VBA-script from another table.

Any ideas?

Thank you very much in advance

Jasper

[John_G]

Hi -

The error message suggests that the sub-query:

(SELECT y.Actuals FROM tblActuals AS y WHERE (y.retailerID=z.retailerID) AND (y.ArticleID=z.ArticleID) AND ((DatePart("ww",z.DateFrom,2,3)-1)=DatePart("ww",y.DateFrom,2,3)))

identifies more than one record, which it must not do because it forms an item (i.e. a field) in the main query. I'm not sure if it matters, but you have "y" and "z" reversed in the datepart bit.

I think the combination of Retailer_ID + Article_ID + DatePart("ww",DateFrom,2,3) in your table tblActuals is not unique. Try running a query with just those three items from that table to see if in fact there are duplicates.

John


John

[jasperk]

Thanks John,

But I really double checked there are no double records, I have only 4 rows at the moment, of which everything is different.
I did notice, that if after appending, I manually alter the table to another value and back to the original, the query does succeed. I checked all formatting, and they are all the same...

Any suggestions?

Jasper

[John_G]

What is the source of the data to be appended, and how do you append it? It sounds as if maybe a date field is not getting the correct value on the append, especially if you "correct" it manually and it works.

John

[jasperk]

Hi John,

I just found I made a stupid mistake.
The data was imported from SAP in the following format "1.2011", so weeknumber.Year... I formatted this as a number and did a split on left(week,2), but therefore received for weeks 1-9 also a "2" for 2012

However, now this is fixed, I still receive errors. I copied the articleID, retailerID and DateFrom to excel and ran a remove duplicates, and conditional formatting... this returned no duplicates.

When I now append only 50 rows, it works, but when I append 100 it fails, can it be dependent on size?

Thanks

[jasperk]

I made some alterations, and suddenly it worked. Don't know what I did, but it works now.
Thanks for your time