UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Advanced Ranking Question, Access 2010    
 
   
bntringa
post Apr 15 2018, 04:57 PM
Post#1



Posts: 215
Joined: 4-February 04



Hey all,

I am looking to create a query/series of queries to accomplish the following ranking requirements.
- 1st place score receives 30 points
- subsequent places receive previous place score minus 2 (e.g. 28, 26, 24, 22)
- ties are handled by adding the assigned scores and dividing by number of rows (e.g. 3 way tie for 2nd place would be (28+26+24)/3) Each row receives 26 points
- NOTE: there are never more than 15 teams in a series, so last place team would receive 2 points

Trying to wrap my head around the best solution, whether by query or series of queries, perhaps a VBA module?

This process will update a table as follows:

TeamID TeamScore TeamPoints
1 100 30
2 98 26 (3 way tie)
3 98 26 (3 way tie)
4 98 26 (3 way tie)
5 95 22
6 90 19 (2 way tie)
7 90 19 (2 way tie)
8 85 16
9 80 14
10 75 12


Thanks for your thoughts on this!
Go to the top of the page
 
RJD
post Apr 15 2018, 05:41 PM
Post#2


UtterAccess VIP
Posts: 8,237
Joined: 25-October 10
From: Gulf South USA


Hi: Looking at your data, do you already have the TeamIDs, or do you need to start with creating the TeamID?

I assumed you already know how to or already have gotten the 1, 2, 3, etc. TeamID values. And I went from there. If this is not correct, you can start with the ranks then use the queries in my demo.

Take a look. See how the queries build up. I left them separate for development/viewing purposes, but you could use the subquery approach to reduce this to one query.

You could do this as well using VBA, but the query approach seemed to fall out without a problem, once the calculations were understood.

HTH
Joe
Attached File(s)
Attached File  AdvancedRanking.zip ( 21.1K )Number of downloads: 11
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Apr 15 2018, 09:37 PM
Post#3


UtterAccess VIP
Posts: 8,237
Joined: 25-October 10
From: Gulf South USA


Hi: Just thought I'd add how you might do this if you DON"T have the 1, 2, 3, etc. rankings to start with ... in two query steps. I scrambled the TeamIDs you displayed so that they were not 1, 2, 3 etc., then calculated the ranks, counts of ranks and points in one query. Then tied that to the table to get the complete results.

See the attached demo.

HTH
Joe
Attached File(s)
Attached File  AdvancedRanking_Rev1.zip ( 19.12K )Number of downloads: 5
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ADezii
post Apr 16 2018, 09:56 AM
Post#4



Posts: 1,885
Joined: 4-February 07
From: USA, Florida, Delray Beach


  1. Very interesting Thread. My goal was to create a purely Code-based approach that would involve no Queries, and I feel that I have achieved this. There is only the Table containing the actual Data (tblData) as well as a Public Sub-Routine (AdvanceRanking()). Granted, this approach may be a little unorthodox, but you can judge for yourself. The Output can easily be entered into a Table if so desired.
  2. Table Data (tblData):
    TeamIDTeamScore
    1100
    298
    398
    790
    885
    980
    1075
    498
    595
    690
    1199
    1260
    1390
    1483
    1577
  3. Sub-Routine Definition:
    CODE
    Public Sub AdvanceRanking()
    Dim MyDB As DAO.Database
    Dim rst As DAO.Recordset
    Dim intRecNum As Integer        'Will relate to Ranking
    Dim intFreq As Integer          'Number of Ties
    Dim intTiePts As Integer
    Dim intTieCtr As Integer        'Counter position within Ties

    Set MyDB = CurrentDb
    Set rst = MyDB.OpenRecordset("SELECT * FROM tblData ORDER BY [TeamScore] DESC,[TeamID]", _
                                  dbOpenSnapshot)

    Debug.Print "Team", "Score", "Freq", "Points"
    Debug.Print "----------------------------------------------------"

    With rst
      Do While Not .EOF
        intRecNum = intRecNum + 1       'reflects the actual Rank
          intFreq = DCount("*", "tblData", "[TeamScore] = " & ![TeamScore])
            If intFreq > 1 Then     'Ties
              intTieCtr = intTieCtr + 1     '1st Tie, 2nd, 3rd, etc.
                If intTieCtr = 1 Then       '1st Tie
                  intTiePts = 30 - ((intRecNum - 1) * 2) - (intFreq - 1)    'Points for 1st Tie based on Rank
                    Debug.Print ![TeamID], ![TeamScore], intFreq, 30 - ((intRecNum - 1) * 2) - (intFreq - 1)
                Else        'Consecutive Tie(s)
                  Debug.Print ![TeamID], ![TeamScore], intFreq, intTiePts
                End If
            Else    'No Ties, calculate Points based on Ranking/Record#
              intTieCtr = 0
                Debug.Print ![TeamID], ![TeamScore], intFreq, 30 - ((intRecNum - 1) * 2) - (intFreq - 1)
            End If
            .MoveNext
      Loop
    End With

    Debug.Print "----------------------------------------------------"

    rst.Close
    Set rst = Nothing
    End Sub
  4. OUTPUT:
    CODE
    Team          Score         Freq          Points
    ----------------------------------------------------
    1             100             1             30
    11            99              1             28
    2             98              3             24
    3             98              3             24
    4             98              3             24
    5             95              1             20
    6             90              3             16
    7             90              3             16
    13            90              3             16
    8             85              1             12
    14            83              1             10
    9             80              1             8
    15            77              1             6
    10            75              1             4
    12            60              1             2
    ----------------------------------------------------

P.S. - A MAXIMUM of 15 Teams is assumed.
Go to the top of the page
 
bntringa
post Apr 16 2018, 04:06 PM
Post#5



Posts: 215
Joined: 4-February 04



Thanks Joe for your quick response on this! So your revised example works great and as expected. To clarify your assumption, the TeamID truly is a random ID (not a ranking indicator) - in my sample data I should have randomized those IDs.

This example works perfectly for one week's worth of data. So to add a layer to the onion, the table that I have actually has records for multiple weeks using WeekID to differentiate. Can we tweak your query to also include the WeekID and reset the points for each iteration of WeekID in the table?

Thanks again!

Sample data here:
WeekID TeamID TeamScore Points
1 5 100 30
1 7 95 27
1 6 95 27
1 2 80 24
1 1 70 22
2 6 99 30
2 1 97 28
2 2 96 26
2 9 92 23
2 3 92 23
Go to the top of the page
 
bntringa
post Apr 16 2018, 04:08 PM
Post#6



Posts: 215
Joined: 4-February 04



ADezii - thanks also for your input on this! I've always enjoyed the collaborative and helpful environment of this site and willingness of complete strangers to lend a hand! As a student of Access, I am always interested in the different approaches recommended, as there are almost always multiple ways to address a given issue. I am definitely going to dig into your code based solution as well - thanks again!
Go to the top of the page
 
RJD
post Apr 16 2018, 10:20 PM
Post#7


UtterAccess VIP
Posts: 8,237
Joined: 25-October 10
From: Gulf South USA


Hi again: All you need to do is add the WeekID to the queries (with GROUP BY in the first) with a criteria of something like [Enter Week ID] (or use a control in a form to enter it and reference the form control instead of using the prompt). Then accommodate that criteria in the two domain functions.

That should do it.

Given that you are calculating for 15 records or fewer, the domain functions should not slow you down.

HTH
Joe
Attached File(s)
Attached File  AdvancedRanking_Rev2.zip ( 19.42K )Number of downloads: 6
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
bntringa
post Apr 17 2018, 02:27 PM
Post#8



Posts: 215
Joined: 4-February 04



That's perfect - thanks again Joe!
Go to the top of the page
 
RJD
post Apr 17 2018, 03:40 PM
Post#9


UtterAccess VIP
Posts: 8,237
Joined: 25-October 10
From: Gulf South USA


My pleasure. Good luck with your project.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th April 2018 - 10:13 AM