467,913 Members | 1,823 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,913 developers. It's quick & easy.

Rank calculated values

I'm new to Access and trying to learn with 2003. I have built a spreadsheet in excel that evaluates the effectiveness of monthly cost forecasting based on actual reported costs. We forecast up to 12 months out so my spreadsheet has to evaluate by forecasted month out for every project on file (~ 600 projects). The number of calculations necessary to run this workbook make excel an unattractive option on an ongoing basis.

I've imported all of my tables into access and performed the necessary actual / forecast calculations which gives me a table that I have titled SourceData_calcs. My next step will be to rank each forecasted month out results so that I can create a standard deviation chart with median and mean values plotted for each month. I've tried to find Rank examples online but cannot make the query formulas work when I type them into the "Field:" cell for each column, or when I create another column.

My solution in excel was to calculate each value, count the number of non null values, list all non null values using VLOOKUP, then RANK my list of non null values. I'm really hoping that there is one formula or query that I can write in Access to perform those iterations but I don't speak SQL.
Oct 27 '11 #1
  • viewed: 2878
Share:
1 Reply
ADezii
Expert 8TB
I take a rather unorthodox approach for calculating Ranks based on a specific Field.
  1. In the Table whose Field you wish to Rank, create a Field named [Rank] {LONG}.
  2. Call a Function and pass to this Function three Arguments: Table Name, Name of Field to Rank, and the Name of the Primary Key.
  3. Within this Function a Recordset is created with the Field to Rank in Descending Order as the Primary Sort, then sorted by the Primary Key Ascending as the Secondary Sort. NULLs are not Ranked.
  4. The Rank Field created earlier is now populated with incremental and sequential Ranks.
  5. The Secondary Sort (PK) assures that Ties are not ranked equally, the Record(s) entered earlier will win the Tie(s).
  6. All Data, along with Ranking Values, are now contained in the Table allowing for great flexibility.
  7. I'll post the Function Definition and Call below, but download the Attachment to see what is really going on.
  8. Processing Time was approximately .250 seconds to Rank 2,154 Records.
  9. Function Definition:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fAssignRank(strTableName As String, strFieldToRank As String, strPrimaryKey As String)
    2. Dim MyDB As DAO.Database
    3. Dim rst As DAO.Recordset
    4. Dim strSQL As String
    5. Dim lngCtr As Long
    6.  
    7. strSQL = "SELECT * FROM [" & strTableName & "] WHERE [" & strFieldToRank & _
    8.          "] IS NOT NULL ORDER BY [" & strFieldToRank & _
    9.          "] DESC, [" & strPrimaryKey & "]"
    10.  
    11. Set MyDB = CurrentDb
    12. Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
    13.  
    14. With rst
    15.   Do While Not .EOF
    16.     lngCtr = lngCtr + 1
    17.       .Edit
    18.         ![Rank] = lngCtr
    19.       .Update
    20.         .MoveNext
    21.   Loop
    22. End With
    23.  
    24. rst.Close
    25. Set rst = Nothing
    26. End Function
  10. Function Call:
    Expand|Select|Wrap|Line Numbers
    1. Call fAssignRank("Order Details", "UnitPrice", "OrderID")
P.S. - For the sake of brevity and simplicity, I've have not included any Validation or Error Checking Code.
Attached Files
File Type: zip Rank.zip (57.7 KB, 170 views)
Oct 28 '11 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by SJH | last post: by
3 posts views Thread by Jeremy Weiss | last post: by
2 posts views Thread by Jim.Mueksch | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.