Rank calculated values

Jeff St Andre

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

ADezii

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.

Oct 28 '11 #2