Select Top 4 within a single record?

[Bob4grave]

I've done Top 3 queries in a field across records before, but this is a different situation. After a crosstab query, I've got each record consisting of a ClassCode and WholeName and that person's scores for seven meets (for Meet1, Meet2, etc).

The problem is summing. I can do a straight sum automatically, but instead I need to select the Top 4 scores per record and sum them into a SeasonScore. In Excel I'd just use the LARGE function to select & sum the 3 largest in a row, but that's not an option in Access. What is?

As a bonus, I'd also like to rank DESC by SeasonScore, but I can figure that out if I just get Top 4 working correctly in a row...

thx!

 

[Bob4grave]

I just decided to redo the approach, handling it upstream before the crosstab as a routine Top 4 query. (No more trying to pound in screws with a spanner).

So now the anomaly is a bit more mainstream. A Top 4 query will still result in 5 results on rare occasions if there is a tie score on result #4. How do I break that tie automatically so Select Top 4 returns only 4 values? (NOTE: Ties for value 1, 2, or 3 are fine; I just want the total values not to exceed 4.)

Bob

 

[jdraw]

see this article - Allen discusses ties
http://allenbrowne.com/subquery-02.html

 

[mdlueck]

Seems like you are looking for:

Select the TOP n Rows For Each Group
http://archive.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=SelectTopNByGroup

 

[Bob4grave]

Thanks, JDRAW. Relevant article, but I can't make it apply. The ordering is happening, but selection on Top N is still N+1 in case of a tie at place N.

Here's what I'm working with:

SELECT tblFinishMaster.CourseName, tblFinishMaster.ClassCode, tblFinishMaster.School, tblFinishMaster.WholeName, tblFinishMaster.Points, tblFinishMaster.MeetName
FROM tblFinishMaster
WHERE ((tblFinishMaster.Points) In (SELECT TOP 4 Points
FROM tblFinishMaster AS Dupe
WHERE Dupe.WholeName = tblFinishMaster.WholeName
ORDER BY Dupe.WholeName, Dupe.Points DESC, Dupe.MeetName))
ORDER BY tblFinishMaster.ClassCode, tblFinishMaster.WholeName, tblFinishMaster.Points DESC , tblFinishMaster.MeetName;

Bob

 

[Bob4grave]

Appreciate the reference, MDLUECK, but that doesn't address ties, and especially not last-value ties, which are the core issue.

 

[Bob4grave]

Since the core issue has changed, I'm going to move it out under a different header...

 

[jdraw]

Here's Allen's comment about ties in that article:

TOP 1 is not enough! If two records tie, Access cannot decide between them. Unlike some other databases, it returns both. The trick is to give JET a way to choose between equals. To do that, include the primary key in the ORDER BY clause like this:
(SELECT TOP 1 OrderDate FROM Orders ORDER BY OrderDate DESC, OrderID)
Since the primary key value is unique, Access can decide which is the TOP 1, even if you have multiple orders on the same date.

 

[jdraw]

Can you post a database with relevant table entries to wok with the query?

 

[Bob4grave]

Absolutely! Thanks for asking. The query in question is qrySeasonIndTop4, which identifies the top 4 scores/person for the year.

A quick check if qrySeasonIndTop4 is working is the last field (count of Meets) in qrySeasonIndTop4Sum. If that field filters any meet counts of 5 on a top 4 select, then it's got some wrong data, likely due to ties on the 4th score.

Many thanks for your help.

Bob

 

[jdraw]

It appears you do not have a Primary key in
tblFinishMaster

I made FinishID a PK and got attached result (first page only)

 

[Bob4grave]

Thanks, jdraw. FinishID is indeed the intended primary key. But the results shown here in the graphic don't get into the problem set. Of 300+ participants, only 5 have a tie at the 4th score.

The quickest way to see if there's still a problem is to go to qryIndTop4Sum and filter "Count of Meets" down to 5. If that's even an option, then the Top 4 query in qryIndTop4 is picking up 5 meets for some participants. I'm seeing that even with FinishID designated as a primary key.

Bob

 

[jdraw]

Bob,
I did look at that after I saw your post. And I did see some 5s in the Sum query -
I'll look at it and get back a little later. Have to go out for an hour or so at the moment.

I'm not as familiar with the data as you are, so am looking at it a little blind. From a logic view if you select the top 4 based on points, you will get the 4 highest point values (including ties could get more than 4).
However, if you select the Top 4 FinishIds for the records that have been ordered by Points desc, you should/will only get 4 FinishIds.

I have set up parallel queries Query2j and Query2jSum as my revised versions of your
qrySeasonIndTop4 and qrySeasonIndTop4Sum respectively.

I am attaching the out put of these queries for your review, since you know the data.
If these results meet your expectations, let me know and we can got through the query set up and the changes. If these results are not what you expected, please let me know and we can work to find a solution.

 

[Bob4grave]

jdraw, these are perfect results. I am slowly learning the importance of the primary key--not just having it but actually using it! How you did so in this case would be very interesting to me.

I'm going to try selecting by FinishID ordered by Points, instead of selecting Points, so see if that's where your'e headed...

Thanks,
Bob

 

[Bob4grave]

Hmm. With my Access newbie prowess, I'm getting a bunch of null results. Looks like I'll need step-by-step guidance on this one.

 

[jdraw]

Bob,
Just got back. I saw the NULL also. I'm adjusting the queries to not include any points where points is null.

Here are the queries with the Not Null adjustments.

Query2j:

SELECT tblFinishMaster.CourseName, tblFinishMaster.ClassCode
, tblFinishMaster.School, tblFinishMaster.WholeName
, tblFinishMaster.Points, tblFinishMaster.MeetName, tblFinishMaster.FinishID
FROM tblFinishMaster
WHERE (((tblFinishMaster.FinishID) In 
     (SELECT TOP 4 finishid                       
     FROM tblFinishMaster AS Dupe                              
     WHERE Dupe.WholeName = tblFinishMaster.WholeName and 
     points is not null
     ORDER BY  Dupe.WholeName, Dupe.Points DESC,FinishId)))
ORDER BY tblFinishMaster.ClassCode, tblFinishMaster.WholeName, tblFinishMaster.Points DESC , tblFinishMaster.FinishID;

Query2jSum:

SELECT query2j.CourseName, query2j.ClassCode
, query2j.School, query2j.WholeName, Sum(query2j.Points) AS SumOfPoints
, Count(query2j.MeetName) AS CountOfMeetName
FROM query2j
where points is not null
GROUP BY query2j.CourseName, query2j.ClassCode, query2j.School, query2j.WholeName
ORDER BY query2j.ClassCode, Sum(query2j.Points) DESC;

Nohte: I haves updated these queries since I sent you the zipped results.
These queries should not include any records whith NULL points.

I'm not convinced that the FinishId is needed in the Order By in Query2j, but I haven't played with that.

Good luck.

 

[Bob4grave]

PERFECT! Actually my null issue was the rookie error of saying "WHERE (((tblFinishMaster.Points) In (SELECT TOP 4 finishID", which obviously has zero matches, but I'm really glad you showed me how to use "and X is not null" anyway, as I may need it later elsewhere.

Totally solved. Many thanks.

 

[jdraw]

glad to help,