Subquery

[TrekBiker]

This query is giving error 'Query input must contain at least one table'.

SELECT tblCumulativeScores.Player, tblCumulativeScores.CompDate, tblCumulativeScores.Points
(SELECT TOP 3 tblCumulativeScores.Points
FROM tblCumulativeScores as Dupe
WHERE (((Dupe.CompDate)=tblCumulativeScores.CompDate))
ORDER BY tblCumulativeScores.Points DESC);

Why is this?

 

[kray4660]

I does not look a valid SQL statement, I think you are missing a FROM after the tblCumulativeScores.Points, but something else is still missing.

 

[TrekBiker]

Thanks for response.
What I'm trying to do is extract from tblCumulativeScores the top 3 placings, with ties, for each CompDate.
Clearly struggling to make it work, so alternative approaches welcomed.

 

[dhookom]

I would try;

SELECT tblCumulativeScores.Player, tblCumulativeScores.CompDate, tblCumulativeScores.Points
FROM tblCumulativeScores
WHERE tblCumulativeScores.Points In
(SELECT TOP 3 tblCumulativeScores.Points
FROM tblCumulativeScores as Dupe
WHERE Dupe.CompDate=tblCumulativeScores.CompDate
ORDER BY tblCumulativeScores.Points DESC);

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Thanks, that produces a result at least, but isn't selecting the top 3 Points for each date.

I'm wanting just from Simon C to James for this date.

 

[Andrzejek]

top 3 Points " for 04-May-19 would be: 37, 34, and 33, so you want "from Simon C to " Simon B, right

---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

What is the primary key field in tblCumulativeScores?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[TrekBiker]

Andrzejek, I want the top 3 places, including ties, so not top 3 Points. In the example Simon C and Danie were in joint first place, and Andy, Lance and James were in joint third.

Duane,tblCumulativeScores is fed by an append query based on data in a form that does a number of calculations to arrive at a points score for each player taking part on a CompDate for an event with a RoundID, which is an Autonumber / Primary Key obtained from a tblRound.

This will sound confusing but tblCumulativeScores doesn't have its own Primary Key, since unique records are being appended into it. From tblCumulativeScores I want to find out who was first, second and third on each date.

 

[dhookom]

You need a unique value/expression:

SELECT tblCumulativeScores.Player, tblCumulativeScores.CompDate, tblCumulativeScores.Points
FROM tblCumulativeScores
WHERE [highlight #FCE94F][unique value/expression][/highlight] In
(SELECT TOP 3 [highlight #FCE94F][unique value/expression][/highlight]
FROM tblCumulativeScores as Dupe
WHERE Dupe.CompDate=tblCumulativeScores.CompDate
ORDER BY tblCumulativeScores.Points DESC);

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016