Help with "At most one record can be returned using this subquery" (1 Viewer)

Tuux

New member
Local time
Today, 22:09
Joined
Nov 17, 2020
Messages
2
Hi

Thanks in advance for helping.

I'm using the following query to grab the most recent price from TR-Prices, this query (Generate-Latest-Prices) is still working
TR Prices contains three columns, SKU DATE PRICE
Code:
 (SELECT TOP 1 Price FROM [TR-Prices] AS T1 WHERE T1.SKU = [TR-Prices].SKU AND T1.Date=(SELECT MAX(Date) from [TR-Prices] AS T2 WHERE T2.SKU = T1.SKU) ORDER BY T1.Price DESC)
I have another query which then pulls the latest price from Generate-Latest-Prices which has been working perfectly.

However, since adding more prices/dates, I'm now getting this error on the second query, which is querying Generate-Latest-Prices to get the latest price:
"At most one record can be returned using this subquery"

Aside from adding more data into the DB, I can't think of anything else which has changed to cause this error.

Any query trying to pull the latest price from generate-latest-prices is getting this error, none where before the new prices where added

Any help will be greatly appreciated, as I'm currently pulling my hair out trying to figure this out!

Kind regards
 

Isaac

Lifelong Learner
Local time
Today, 14:09
Joined
Mar 14, 2017
Messages
3,763
I believe that in Access, top 1 does not guarantee you will not get more than 1.
 

Minty

AWF VIP
Local time
Today, 22:09
Joined
Jul 26, 2013
Messages
8,020
If you have a duplicate date in your T2 sub query for a SKU it will error out.

Double check your data.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 14:09
Joined
Oct 29, 2018
Messages
14,095
Hi. Welcome to AWF!

Just to be clear, you are not getting the error with just using that query, right? You are getting the error when you use that query as either a subquery or a join to another query, correct? If so, can we please see an example of that query? Thank you.
 

Tuux

New member
Local time
Today, 22:09
Joined
Nov 17, 2020
Messages
2
If you have a duplicate date in your T2 sub query for a SKU it will error out.

Double check your data.
THANK YOU!

I had two rows in Prices with the same SKU+DATE, the error explained the problem.

Problem solved
 

Isaac

Lifelong Learner
Local time
Today, 14:09
Joined
Mar 14, 2017
Messages
3,763
That's an example of a time when Access will return more than 1 for a top 1 query.
Annoying as heck.
 

Minty

AWF VIP
Local time
Today, 22:09
Joined
Jul 26, 2013
Messages
8,020
That's an example of a time when Access will return more than 1 for a top 1 query.
Annoying as heck.
In this particular example, I don't think its the TOP one that's the problem but that it ends up joined to two results, so it doesn't know which one to select?
 

Isaac

Lifelong Learner
Local time
Today, 14:09
Joined
Mar 14, 2017
Messages
3,763
In this particular example, I don't think its the TOP one that's the problem but that it ends up joined to two results, so it doesn't know which one to select?
I'd call them one and the same. in cases with 2 identical records, TOP 1 (in Access) doesn't actually return top 1, it returns top 2
 

Minty

AWF VIP
Local time
Today, 22:09
Joined
Jul 26, 2013
Messages
8,020
#Pedant Mode On# 😁
But poor old Access hasn't been told how to get a unique top 1 and is confused...
;)
 

Users who are viewing this thread

Top Bottom