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_calc s. 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.

[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:
   

   Public Function fAssignRank(strTableName As String, strFieldToRank As String, strPrimaryKey As String)
   Dim MyDB As DAO.Database
   Dim rst As DAO.Recordset
   Dim strSQL As String
   Dim lngCtr As Long
   
   strSQL = "SELECT * FROM [" & strTableName & "] WHERE [" & strFieldToRank & _
            "] IS NOT NULL ORDER BY [" & strFieldToRank & _
            "] DESC, [" & strPrimaryKey & "]"
   
   Set MyDB = CurrentDb
   Set rst = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
   
   With rst
     Do While Not .EOF
       lngCtr = lngCtr + 1
         .Edit
           ![Rank] = lngCtr
         .Update
           .MoveNext
     Loop
   End With
   
   rst.Close
   Set rst = Nothing
   End Function

10. Function Call:
    

    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.