Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2009
    Posts
    3

    Unanswered: Update a field based on content of most recent 7 entries of another field...

    I have a pool league that I am a part of that wants to create a db to track league member results. I have the wins/losses working but one thing the president wants the db to take care of is the player rankings.

    In our leage a player gets a rank to start based on his/her skill. That rank can change up or down based on the last 7 games they have played. For example if a player is ranked a 4 in 8-Ball to start, then they play seven games and out of that 7 games they win 5 games. Their rank would be increased to a 5. If they were to loose 5 games then their rank would be reduced to a 3.

    Does anyone have any ideas on how I can do this?

  2. #2
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580
    The rank can allways be calculated and therefore would never saved in the database as a field, just calculated with a query.

    Can you post a .zip of your database or table structure?
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  3. #3
    Join Date
    Aug 2009
    Posts
    3
    I don't have my db with me right now but the structure so far is very basic:
    MEMINFOTable:member name, team name, rank
    MEMW-LTable:member name, game date, result
    TEAMINFOTable:team name, member name, team wins, team losses

    I need to use the "rank" field in the MEMINFOTable beacuse my president also wants to be able to change a players rank manually based on his opinion of the player.

    What I want is a query to look at the most recent 7 "results" in the MEMW-LTable and then update the "rank" in the MEMINFOTable accordingly.

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    In that case, I would tackle the two concepts completely differently. You have a calculated/projected rank, and then you have the REAL rank. Give your boss a query that gives him the projected rank, then store an "actual rank" as it's own (non-calculated) field.

    You might want to check with the APA/BPA too. I remember seeing some pre-baked utilities ready to go for automated league and rank management.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  5. #5
    Join Date
    Aug 2009
    Posts
    3
    So how would I write the formula to only look at the most recent 7 wins/losses? The reason I say "most recent" is becaue eventually the player will have about 25-30 games in a session but I only want to base their rank on the previous 7 games.

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You can use the "TOP" keyword to limit results in any query.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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