CrossTab Query Max X Returns

[systems013]

Hi All,

I am trying to find an Access solution to handling the MAX 2/3rds (I.E. 2from3 or 8from12 etc) returns from a series of "Scores" of a league table which will have many players, playing in many matches. I have created a crosstab query which delivers the total scores for each player from each game and played around with a make table query and a report output which I have used in excel to deliver the best 2/3rds of the scores. As others will be using the completed data base I would like to keep the solution within Access but I cannot work out how or if this is possible. Below is a limited output from the crosstab query showing the Total of all games played which I wish to turn into best 2/3rds.

Any help greatly appreciated.



David

[June7]

I don't understand what is meant by 'MAX 2/3rds (I.E. 2from3 or 8from12 etc)'. Want results for each league or all leagues overall?

Show a limited sample of source data and what the results would look like. I.e., from the data you posted, what would be the best 2/3rds?

[systems013]

Hi June7,
In the simple Crosstab extract I have shown we have 5 league results for each player with the Totals shown. I.E. for the first player we have a total of 98 made up from the "SUM" of 18+20+20+20+20. I need to find a way of picking the top 2/3rds of each players results hence in the case shown above we would have a total of 80 I.E. dropping off the lowest result of 18. If there was another result in the players list making six in total then we would be dropping off two results I.E. two from the six and so on.

In Excel I would use =+MAX(C3:G3,2) to give me the second largest score in an array and =+MAX(C3:G3,3) for the third largest score and by continuing this theme I can make up the best of three, six, nine results etc.

I hope this makes sense!

David

[June7]

Review this thread. It pretty much says what I think http://bytes.com/topic/access/answer...-percent-group

[systems013]

Hi June7,

Appart from saying thank you for taking the time to have a look at this for me I am at a loss as to why such a simple problem appears to have no solution. I had already looked at the link you passed to me yet still considered the problem one that Access should be able to deal with one way or another.

Why the Crosstab query is limited to the offerings as per the snapshot below is not known as I would have thought a more extensive/flexible choice could have been made available. In order to progress my project I will use Excel as suggested and just keep prodding at an Access solution no doubt through some lengthy coding.

Thanks again David

[June7]

There are solutions in Access, just not a simple one.