Select & total 4 highest point earners in a team

[dsmith910]

Hi

I have an Access 2000 Database which holds (amongst other things) the number of points earned by each member of a number of different teams.

Somehow I need to be able to take the top 4 point earning members of a team, add their points together and show the total against the team.

Each team can have a varying number of members – anything from 4 to 50, or more!

At the moment I can get Access to total all points earned by a particular member for one team. I then copy the whole lot to an Excel spreadsheet and ‘manually’ calculate the total of the highest 4 sets of points against each team. This is time consuming, to say the least.

There must be a better way?

Thanks

Doreen

 

[SkipVought]

Hi,

Show me an example of your data in Excel. That will dictate how to proceed.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[ETID]

You could use an ODBC link to bring your query directly into excel.

in a blank excel worksheet, from the menu...

Data>get external data>new database query...follow the wizard, and when prompted select the use the MS query option,(a gui similar to access),...carefully select options you want on each window,...such as "read only" check boxes and what not...
then return data to ms excel.

Note: If you opt to link to an access query, be sure to remove any prompt parameters from the access query and redefine them in MSQuery.

when the data is returned, you can right click in the data area on the worksheet and set properties such as refresh on open, auto fill formulas and others.


now when ever the database is updated the workbook will reflect the changes,if you refresh...auto(on open) or manually.

 

[TonyJollans]

Hi Doreen,

I don’t think there’s a direct way of doing this but here’s one way of doing it in code

If you have, amongst others, . . .

a Teams Table with Columns Team and Top4Total

and a Players Table with Columns Team and Points

Then this should update the totals for you

 Dim tjDatabase As DAO.Database
Dim tjRecordset_Teams As DAO.Recordset
Dim tjRecordset_TopFour As DAO.Recordset

Dim tjSelect_TopFour As String
Dim tjTopFourTotalPoints As Long

Set tjDatabase = CurrentDb
Set tjRecordset_Teams = tjDatabase.OpenRecordset("Teams")

With tjRecordset_Teams

    .MoveFirst

    While Not .EOF
        
        tjSelect_TopFour = "SELECT TOP 4 [Players].[Points] FROM [Players]" & _
                           " WHERE [Players].[Team] = """ & tjRecordset_Teams!Team & """" & _
                           " ORDER BY [Players].[Points] DESC;"
        Set tjRecordset_TopFour = tjDatabase.OpenRecordset(tjSelect_TopFour, dbOpenDynaset)
        
        With tjRecordset_TopFour
        
            .MoveFirst
            tjTopFourTotalPoints = 0
            
            While Not .EOF
        
                tjTopFourTotalPoints = tjTopFourTotalPoints + !Points
                .MoveNext
        
            Wend
            
        End With
        
        .Edit
        !Top4Total = tjTopFourTotalPoints
        .Update
    
        .Close
        
        .MoveNext
    Wend

    .Close

End With

Set tjRecordset_TopFour = Nothing
Set tjRecordset_Teams = Nothing
Set tjDatabase = Nothing

Enjoy,
Tony

 

[TonyJollans]

Hi Doreen,

Sorry, one of the problems of tidying up code after you test it is that you introduce errors. I've got a line in the wrong place. Please try this amended version - moved line highlighted.

Dim tjDatabase As DAO.Database
Dim tjRecordset_Teams As DAO.Recordset
Dim tjRecordset_TopFour As DAO.Recordset

Dim tjSelect_TopFour As String
Dim tjTopFourTotalPoints As Long

Set tjDatabase = CurrentDb
Set tjRecordset_Teams = tjDatabase.OpenRecordset("Teams")

With tjRecordset_Teams

    .MoveFirst

    While Not .EOF
        
        tjSelect_TopFour = "SELECT TOP 4 [Players].[Points] FROM [Players]" & _
                           " WHERE [Players].[Team] = """ & tjRecordset_Teams!Team & """" & _
                           " ORDER BY [Players].[Points] DESC;"
        Set tjRecordset_TopFour = tjDatabase.OpenRecordset(tjSelect_TopFour, dbOpenDynaset)
        
        With tjRecordset_TopFour
        
            .MoveFirst
            tjTopFourTotalPoints = 0
            
            While Not .EOF
        
                tjTopFourTotalPoints = tjTopFourTotalPoints + !Points
                .MoveNext
        
            Wend

.Close

        End With
        
        .Edit
        !Top4Total = tjTopFourTotalPoints
        .Update

' .Close was (wrongly) here

        .MoveNext
    Wend

    .Close

End With

Set tjRecordset_TopFour = Nothing
Set tjRecordset_Teams = Nothing
Set tjDatabase = Nothing

Enjoy,
Tony

 

[dsmith910]

Hi Guys

The dbase has a number of related tables amongst which are:

Teams: TeamName; TeamRegNo; Captain etc
Players: PlayerRegNo; PlayerName; TeamRegNo etc.
Points: PlayerRegNo; PointsEarned; TournamentID
Etc:

Tony - where do I put the above code to try it out? Is it to go in a macro? Do I then run a report with the macro in it or can I put it in a query somehow.

Am I right in thinking that its adding the total of the points gained by the top 4 players in one cell for each team?

As you may be able to tell, my knowledge so far for Access is limited - but I'm learning fast!

Thanks

Doreen

 

[TonyJollans]

Hi Doreen,

To run my code you need to put it in a procedure in a module, so ..

Select Modules in the Database Window
Click on New
In the code window which opens up, create a Sub - just type these two lines ..
Sub Test()
End Sub
Now cut and paste my code between these two lines

What it does is fill in a column (called Top4Total) in the Teams table with the total I think you want. You must have this column there - all the code does is populate it. Afterwards you can report from it or do anything else you want. But I can't think of any easy way of making it auto-update, so you need to rerun the code if any of the underlying points change.

To be able to test it you will need (obviously) to change the field names, but also I wrote it on the assumption that there were two tables - as you have three, the SQL needs to change

tjSelect_TopFour = "SELECT TOP 4 [Points].[Points] FROM [Players]" & _
                   " INNER JOIN [Points] ON [Players].[Player] = [Points].[Player]" & _
                   " WHERE [Players].[Team] = """ & tjRecordset_Teams!Team & """" & _
                   " ORDER BY [Players].[Points] DESC;"

Enjoy,
Tony

 

[dsmith910]

Hi Tony

Many thanks for this - will try it out and get back to you to let you know how I go on. This is new ground for me in Access so it may take me a day or two to see if I can get it to work.

Thanks again - if I success it will save me hours of boring work.

Doreen