Top Values, grouping

[ChadW]

I have recently begun using access to develop a small golf application to help a golf society I run. I have coding experience but it is with Progress.

It is basically complete, however I have one hurdle I cant seem to get my head around:

I need to create a query that I can output to a report. It needs to show each golfers scores over a period of 24 weeks and sum the top 12 results. additionally, I would like to format the report with each golfers scores one one row (A league table style format).

Any help would be much appreciated, thanks.......

 

[DCrake]

Simple Software Solutions

You could try

SELECT TOP 12 fldPlayer, Sum(fldScore) AS total
FROM TblPlayersScores
GROUP BY fldPlayer SORT BY Sum(fldScore) As Total DESC;

Field and table names used for brevity.

This will group by players names summing up their scores and showing the top 12 scores.

 

[ChadW]

Thanks for your reply, I have tried the code but returns an error message:

Syntax Error (Missing operator).

I have attached the code below:

SELECT TOP 12 Scores.MemberID, Sum(Scores.Points) AS total
FROM Scores
GROUP BY Scores.MemberID SORT BY Sum(Scores.Points) As Total DESC;

 

[Brianwarnock]

It is Order By not Sort By

Brian

 

[MSAccessRookie]

Thanks for your reply, I have tried the code but returns an error message:

Syntax Error (Missing operator).

I have attached the code below:

SELECT TOP 12 Scores.MemberID, Sum(Scores.Points) AS total
FROM Scores
GROUP BY Scores.MemberID SORT BY { Order By } Sum(Scores.Points) As Total DESC;

The Group By statement does not use the As Qualifier the way you have it here. Get rid of the "AS Total" in the Group By statement,and it looks like it should work. Also, do not use SORT BY, Use Order By.

 

[Brianwarnock]

I am not convinced that that will give you what you want, I think that it will give you the top 12 players after all their scores are totalled.

I think that you may need a similar query per player and then a Union qury to bring them all together, or some VBA code, but maybe somebody knows better.

Brian

 

[Brianwarnock]

The Group By statement does not use the As Qualifier the way you have it here. Get rid of the "AS Total" in the Group By statement,and it looks like it should work. QUOTE]

where does he do that?

Brian

 

[MSAccessRookie]

Look at his statement in my response (#5), I highlighted it in Red.

You are right about the query not returning what he wants. I think it will return the top 12 rounds from the whole database and then group them by golfer.

Union queries are best when you know the number of queries being joined. I am looking at other possibilities. VB may be the way to go here and I will defer to the experts if that is the case.

 

[Brianwarnock]

Look at his statement in my response (#5), I highlighted it in Red.

That's not in the Group By its in the Sort By, and I admit to not noticing that as I was busy saying it should be Order By.(that's my excuse and I'm sticking to it )

Otherwise we are in agreement and like you I will defer to those better at VBA.

Brian

 

[ChadW]

Thanks again, you were correct about the output. The query calculates each members full totals and then lists the top twelve members.

 

[MSAccessRookie]

That's not in the Group By its in the Sort By, and I admit to not noticing that as I was busy saying it should be Order By.(that's my excuse and I'm sticking to it )

Otherwise we are in agreement and like you I will defer to those better at VBA.

Brian

You were correct, but so was I... It was the Order By Statement, BUT: Order BY does not use the As Qualifier the way he had it either.

 

[ChadW]

I have tried to use this query on a single members scores but when trying to total the top 12 scores, the total of all that members scores are returned.....help

Code:
SELECT TOP 3 Scores.MemberID, SUM(Scores.Points) AS TOTAL
FROM Scores
WHERE (((Scores.MemberID)=1))
SORT BY Scores.MemberID ORDER BY SUM(Scores.Points);

 

[Brianwarnock]

You need to realise that ACCESS builds the dataset and then applies the Top, you cannot sum a Top so many, you can only sSelect them. You are looking at a multi query solution. I have no data to try this on, maybe you can Compact then zip and attach your DB , but the problem with a non vba solution is that it will be high maintainance as you will need to run a query for each member and thus if the membership changes you will need to add remove queries.

What you would need to do, I think , is

A query for each member select top12 scores, you can apply the date restriction using Dateadd in the criteria of the date field, but need not select that field.

These queries can possibly then have a Union applied, i'm not big on Union queries, or all be append to a temp table.
Then a final query does the Groupby and Sum.

Brian

 

[dallr]

I am not sure what you mean by League table format. But the following should give you the top 12 scores per member along with the total for that member.
I used the same fields you used. I noticed you mentioned something about a number of weeks. You will have to explain more of this for me to tweak the query. But run this as a start.

SELECT S.MemberID
     , S.Points
     , ST.TotalPoints
FROM Scores  S 
     INNER JOIN  (SELECT SUM(S.Points) As TotalPoints, S.MemberID 
                  FROM Scores S
                  WHERE S.Points IN (SELECT TOP 12 SS.Points 
                                     FROM Scores SS 
                                     WHERE SS.MemberID = S.MemberID) 
                  GROUP BY MemberID) ST 
       ON  S.MemberID = ST.MemberID
WHERE (((S.Points) In (SELECT TOP 12 Points 
                       FROM Scores  SSub 
                       WHERE SSub.MemberID = S.MemberID)))
ORDER BY ST.TotalPoints ,  S.MemberID, S.Points;

Dallr

 

[ChadW]

Thanks for your help dallr, the code is working great Just one thing, when requesting the top 'n' points if there are duplicate points values then the query also accumulates these values.

ie. if requesting the top 5 points from a member and his scores are 40,40,39,38,38,38,36, Then his top 5 scores would be 40,40,39,38,38 with a total of 195. However the query returns 40,40,39,38,38,38 (6 results) returning a total of 233.

is there a way to avoid picking up the duplicate scores returned on the bottom value?

The format for the report I would have liked for this would be the members name followed by their scores with a total for their top 12 results at the end, eg.

ChadW 40 34 28 38 39 32 32 30 29 40 35 32 25 37 37 Total

 

[dallr]

Thats one of the downsides of the Top predicate in Access.

The TOP predicate does not choose between equal values. As in your example , if the 12 and 13 highest points are the same, the query will return more than 12 i.e 13 plus records

Dallr

 

[ChadW]

So there is no way really to apply this query in access?

Just a case of exporting the data to excel and using the LARGE function.

 

[LPurvis]

There certainly is - but it assumes you have a (standardly) well designed table.
Jet's TOP predicate does indeed *assume* the With Ties option that needs to be explicitly stated as required in, say, SQL Server.

What I mean by a well designed table is the most fundamental requirement (IMO) - that it has a unique identifier, i.e. a Primary Key.
I would imagine yours would be called something like ScoreID and is very likely just an autonumber (I'd recommend nothing other than this myself).

Assuming that is in place then we can address Dane's example SQL statement.
IMO it should definitely already be including something which it currently missing.
Ordering.
Whenever you're applying a TOP request it is vital that you're explicit about the order in which you expect the rows to be considered.
When you're doing this we would then be explicit that it is the Points which we're ordering by (as we want the *TOP* points).
Once we're doing that it's then trivial to decide we want to include another ordering criteria. This can effectively be considered redundant or random - but is there to handle the case of Ties.

SELECT S.MemberID
     , S.Points
     , ST.TotalPoints
FROM Scores  S 
     INNER JOIN  (SELECT SUM(S.Points) As TotalPoints, S.MemberID 
                  FROM Scores S
                  WHERE S.Points IN (SELECT TOP 12 SS.Points 
                                     FROM Scores SS 
                                     WHERE SS.MemberID = S.MemberID
                                     ORDER BY SS.Points DESC, SS.ScoreID) 
                  GROUP BY MemberID) ST 
       ON  S.MemberID = ST.MemberID
WHERE (((S.Points) In (SELECT TOP 12 SSub.Points 
                       FROM Scores SSub 
                       WHERE SSub.MemberID = S.MemberID
                       ORDER BY SSub.Points DESC, SSub.ScoreID)))
ORDER BY ST.TotalPoints ,  S.MemberID, S.Points

However...
All that said - you're goal is to get this (concatenated) into a Report?
You could leave some of the summary information to the Report and the concatenation of the individual scores too even.
(Alternatively there are standard and simple recordset concatenation methods to achieve what you want).
i.e. ChadW 40 34 28 38 39 32 32 30 29 40 35 32 25 37 37 Total

 

[dallr]

Leigh I realized that I forgot the Order By Clause (oops). But I was waiting to address this in my reply.
But for the life of me i could not remember how to remove the ties I know i did it before. Thanks for ticking the ole brain!!

Dane

 

[LPurvis]

Oh you don't want to be using an "ole brain".
It'll unecessarily bloat out of control.
Switch to an Linked, Attachment or BLOB brain instead.