Results 1 to 6 of 6
  1. #1
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Numbering Records returned from a query

    Presently writing a custom program for a golf course. One of the requirements is that after a golfer has entered his or her score for the round (and all of the associated handicaping calculations are performed), a screen is to be displayed showing the golfer's current index, the handicap from each set of tees on the home course, the number of rounds played during the current season, and the golfer's RANKING within the current membership - ie. 74th of 373 male members.

    I have absolutely no problems performing all of the pertinent calculations and then sorting everything in the correct order, but I can't figure out how to display (on the screen) the member's ranking. This ranking, of course, may and probably will change each time the golfer enters a score or other golfers enter their scores. All of this information to be displayed is contained in one table (high score, low score, current index, rounds played, etc.) and the table is updated after every entry.

    Maybe I am missing something very basic, but I don't know how to display the ranking of the golfer on screen. On a printed report, this is not a problem. The report is based on a sorted query and each record that is printed is numbered sequentially.

    But I have yet to figure out how to display this ranking on screen. In other words, how do I display that the golfer currently displayed is ranked ## of ### ? The "of ###" is not a problem (just a count of the records that the query returns). But the first part (the actual record number of the golfer being displayed) has got me tearing my little remaining hair out.

    Thanks in advance for any advice.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbering Records returned from a query

    You can count the number of golfers with a lower handicap and add one to get the golfer

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numbering Records returned from a query

    That will give you the actual ranking, but you'll still have to think about how to handle the situation where two golfers come up with the same ranking. Do the golfers care? Does your client care? Do they want you to provide some indicator of that?
    Charlotte

  4. #4
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numbering Records returned from a query

    Thanks to Claus for his solution. As for your question Charlotte (2 or more golfers with the same ranking), I don't think that this is a big problem. The "ties" can always be broken with a secondary sort based on several different factors - date of last round played, lowest score entered, last name, etc.).

    However, the client also wants to be able to DISPLAY a list of golfers and their rankings on screen. I don't see any way of doing this in a form view. The only solution I can come up with is to open a Report in Print Preview view. Can you think of any way to display this information in a form?

    Again, thanks in advance for any suggestions.
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Numbering Records returned from a query

    You can use Claus's expression in a query to.
    Francois

  6. #6
    Gold Lounger Rebel's Avatar
    Join Date
    Jul 2001
    Location
    Canada
    Posts
    3,024
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Numbering Records returned from a query

    Claus & Francois,

    Thank you so much for your input. Tried this method and it works exactly the way I wanted it to. I can now display the golfer's ranking on his(or her) own individual screen (along with their other pertinent stats) as well as in a form displaying all of the golfers, ranked in order of handicap and displaying their actual rank number. You guys are both geniuses!
    John
    A Child's Mind, Once Stretched by Imagination...
    Never Regains Its Original Dimensions

Posting Permissions

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