Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2010
    Location
    Minnesota
    Posts
    22
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I'm trying to get I think itís called a rolling scale.
    I have 500 records but I would like the rolling scale to rate them from 1-5 with 1 being the best.
    The formula I thought would work was "3Month_0to5Rank: [3MonthRank]/([Past_3_Month#]-1)*5" but turns out if keeps going after 5 or I tried switching the formula it gives me more weird numbers.

    I really appreciate you taking the time for assist me with this problem.

    The formula below worked for a different query but now it is giving me from 0.00125 to 1253.21500

    3Month_0to5Rank: [3MonthRank]/([TotalRecords]-1)*5


    Thanks again
    Corey
    Attached Files Attached Files

  2. #2
    2 Star Lounger
    Join Date
    Dec 2009
    Location
    Manchester, United Kingdom
    Posts
    116
    Thanks
    8
    Thanked 17 Times in 16 Posts
    Without opening your ZIP file I'd say the formula needs to be something on the lines of

    Code:
    3Month_0to5Rank: int(round([3MonthRank]/DMAX("[3MonthRank]","[YourTable]") * 5,0))
    Access ought to optimise that so it only calculated the DMAX once.
    If not, and the query is too slow, create another query that just returns maxrank:MAX([3MonthRank]) and outer join that to your table, using that field instead of the DMAX function. (Outer join - just add the query to the query grid, no join lines)

    Ian

Posting Permissions

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