Access Access: finding best 10 values HELP

[AJGE]

Greetings,

I have a table that tracks records which include an individuals name, contact info, and then a series of 14 fields which each contain a number of points that that person earned at some point.

ie.

NAME email pts1 pts2 pts3 pts4 pts5 pts6 ...........
bob @gmail 289 300 52 197 299 12

I need to find a way to quickly select the best (highest) 10 fields for each individual.
So for bob.... (ie)... id like to look at each of the pts fields, and then select the highest 10 values.
I dont need to see the values... realistically the top 10 are going to get added for a running total of the best 10... so if there is a way to do this in a calculated field, that totally works.

* this is NOT finding the best 10 records, but rather identifying the best 10 field contents for every record.

I know that Excel has a function to determine the Best(n) values accross however many columns.... this is basically what I am trying to create in access.

Is it possible to have access perform something similar to the Best function?

Thanks guys.... much appreciated!

 

[goodfellow]

Hi,
If you really want to do it this way, than you should use VBA:
http://support.microsoft.com/default.aspx?scid=kb;en-us;209857


But it's not the best option. To do it well, you have to reorganize your tables. These 14 scores should be in separate table as rows (not columns, because you are violating one of the database normalization rules).
First table (let's say PLAYERS) should only have 3 columns (playerID, name, email)
Second (SCORES) should have: playerID, measurePointNumber, score
Now you can select top ten scores by grouping by playerID and measurePointNumber. I guarantee that it'll be quicker and cleaner solution.
Cheers.

 

[AJGE]

Hey Goodfellow!

I was thinking I had created a mess for myself with the tables organized the way I had them.

I actually had the data stored both ways... and just wasnt using the points table nearly as effectively as I could have!

Im wondering if you can give me an idea of how to select the best 10 results from a grouped select query? I can group them, and sort for max, min, etc... but I dont know of the function to select the top 10 per person.....

If you can shed some light on that... I think ive got this thing licked!

 

[AJGE]

Ok....

I have stumbled on a number of threads looking at "Top 'N' by Group".

This seems to be the problem I am up against....

I cannot for the life of me figure out how to apply it to my query though...

the SQL of the Query is:

SELECT [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned]
FROM [1 Riders and finishes] INNER JOIN Riders ON [1 Riders and finishes].RiderPlate = Riders.RiderPlate
GROUP BY [1 Riders and finishes].RiderPlate, [1 Riders and finishes].RiderFirst, [1 Riders and finishes].RiderLast, [1 Riders and finishes].RiderSex, [1 Riders and finishes].AgeCategory, Riders.Age, Riders.RiderCategory, [1 Riders and finishes].[Points Earned]
ORDER BY [1 Riders and finishes].RiderPlate, [1 Riders and finishes].[Points Earned] DESC;

I am then trying to apply the Criteria in [Points Earned] using the following....
I think this is where my issue is.... Im a little out of my element here with this coding...

(Select (Top 3 [Points Earned] From 1 Riders and Finishes Query Where _
[Riderplate]=[Riders].[RiderPlate] Order By [Points Earned] Des))