Search results

OK. Solved. This produces rankings where the tie is the top number (1,2,3,3,5): SELECT T.MeetClass, T.WholeName, T.NumResult, T.FinishID, ((SELECT COUNT(*) FROM [qryFindFinishers] T1 WHERE (T.NumResult > 0 AND T.MeetClass = T1.MeetClass AND T.NumResult > T1.NumResult))+1) AS Place FROM...

Okay... simplified the query a bit, removing the brain-dead duplication, but the core issue remains. I see someone else (Coach Ty) had this same question in 2009, but not answered in his thread either. How do a rank a second-place tie as 1, 2, 2, 4 instead of 1, 3, 3, 4? This code results in...

I have a query that ranks Finishers by numeric time (NumResult), including ties. Oddly, it pairs ties at the lower of the two places--ie, if two Finishers tie for 2nd, they would both be given a Place of 3. This should be an easy fix, but I can't see what to tweak. Is this too complex a ranking...

Ah. A bit of context here. These are orienteering races, with competitors finishing in a certain order (Place) and points awarded accordingly (100 for 1st place, 95 for 2nd, etc). So I think the logic concern in your item #1 is met. I finally solved the issue by moving the nz logic into its...

Interesting new development... after a visit to http://www.techonthenet.com/access/functions/advanced/nz.php, I tried using the basis of the nz logic as a new field and it worked: Nz(([tblFinishMaster].[Points]),0) AS Newpts Does this mean that nz can be used only at the field level and not...

Hmm. Same result. Diving into the gritty details, this is a master query that does lookups across five tables for various items, with the following SQL: SELECT qryJoinFinishers.MeetClass, qryJoinFinishers.MeetCode, qryJoinFinishers.ClassCode, qryJoinFinishers.WholeName, qryJoinFinishers.Place...

This is useful and relevant info. Thanks. But it still doens't solve the issue, as I'm getting an empty result set, and I'm wondering if I've combined it with a more fundamental mismatch in the same query. I'm using: Nz(([qryJoinFinishers].[Place]),0) as a criteria in Field: Points Table...

I have a query 3_qryMakeFinishMaster that joins a query of all competitive place results (qryJoinFinishers) with a table that assigns a Point value for each Place (tblPlacePoints). The problem is people who didn't finish, who I want to get Points=0. They show up in qryJoinFinishers with a null...

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.

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, 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...

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...

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...

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

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

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...

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).:rolleyes: 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...

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...

After gradually learning by trial & error on my first Access application--an app for scoring and reporting orienteering competitions--it's gratifying to come across a collection of experts who might help reduce the learning curve. It's been equal parts inspiring and infuriating... :banghead...