Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2013
    Posts
    6

    Unanswered: create a rank system in access report

    I have a report that has 2 fields. Name (concatenate of FirstName and LastName) and Total Points. I want a number rank system for it.

    Example:
    1st Name #1
    2nd Name #2
    3rd Name #3
    3rd Name #4
    5th Name #5 (since there is a tie for 3rd it goes to 5th rank)
    6th Name #6
    6th Name #7
    6th Name #8
    9th Name #9
    etc.

    Any help is appreciated.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    stuff some VBA behind the report to do the rank

    declare some global variables

    dim Position as integer
    dim LastScore as integer 'or whatever datatype makes sense eg double
    dim LastPostion as integer

    in the reports on load event assign initial values to those variables

    Position = 1
    LastPostion = 1
    LastScore = 'wahtever value that reflects the highest possible score, eg if its lowest score wins say -1 , if its a highest percentage 100 and so on

    I was going to suggest add a text control to the report called say ranking

    in the forms detail on format event you need to see if the current score is the samne as the previous score in which case its an =
    if (whateveryourscorecolumniscalled = LastScore then 'we have an equal score
    Ranking.value = LastPosition
    else
    ranking.value = Position
    LastPosition = Position
    endif
    Position = Position + 1
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    6
    Thank you for your reply...but I am completely lost here. I am new to access and have no idea where to start on what you just wrote

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There may be a way of doing this using some fancy SQL tickery, way beynd my knowledge

    Personally I'm am not aware of any way to do this without resporting to palcing some code behind an Access report.

    There is a lot ore to Access than using the standard forms and report designers, you can modify a lot of beviours in Access using code behind event hooks.

    create your report
    place a control to the left of the name control (you may have to jiggle it around to make it line up)

    in the reports design mode, move the cursot to the little box to the left of 'Detail', right click which brings up a list of options, the first one (in Access 2010) is 'Build Event', select that, then select code builder

    underneath the top line 'Option Comapre Database', add 'Option explicit'
    ..this forces the Acces runtime to make certain all variables are declared before first use.

    underneath that add the 3 variable declarations from above

    in the left most combo box select 'Report', the rightmost 'Open'
    in between, add the variablke initialistaion code
    Code:
    Private Sub Report_Open(Cancel As Integer)
    Position = 1
    LastPostion = 1
    LastScore = 'wahtever value that reflects the highest ranked possible score, eg if its lowest score wins say -1 , if its a highest percentage 100 and so on
    End Sub
    in between the detail format event
    Code:
    Private Sub Report_Open(Cancel As Integer)
    if (whateveryourscorecolumniscalled = LastScore then 'we have an equal score
    whateveryourtextcontoliscalled.value = LastPosition
    else
    whateveryourtextcontoliscalled.value = Position
    LastPosition = Position
    endif
    Position = Position + 1
    End Sub
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    There is a Function in the following Link that can be called from the Source Query (needs some preparations in the query) to prepare the Rank List in the Report Record Source Query itself:

    LEARN MS-ACCESS TIPS AND TRICKS - Preparing Rank List

    Check whether it is useful to you without going through lot of complex procedures.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It took time, but here is an (almost) "pure" SQL solution.

    Table: Tbl_Scores
    Columns: name TEXT, Score LONG
    Code:
    Name	Score
    --------------
    a	10
    g	20
    c	30
    k	30
    e	40
    d	50
    r	60
    h	60
    i	70
    This query:
    Code:
    SELECT (SELECT COUNT(*)
            FROM Tbl_Scores as c2
            WHERE Format(c2.Score,'000000') & c2.name < Format(c1.Score,'000000') & c1.name 
            ORDER BY c1.score, c1.name) + 1 AS RowNumber, 
            c1.Name, c1.Score
    FROM Tbl_Scores AS c1
    ORDER BY Format(c1.Score,'000000') & c1.name;
    Yields:
    Code:
    RowNumber	Name	Score
    -------------------------------
    1		a	10
    2		g	20
    3		c	30
    4		k	30
    5		e	40
    6		d	50
    7		h	60
    8		r	60
    9		i	70
    It can handle scores in the range 0 to 1,000,000. Add additional zeroes to the format string for larger numbers.

    Of course, it would have been far easier if the table had an Identity column.
    Have a nice day!

  7. #7
    Join Date
    Jan 2013
    Posts
    6
    thank you! sorry but where does the code go?

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In a query, of course!
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •