Mass calculate MEDIAN of large data set

[MrDeej]

Hello!

I have a table wich contains product number and different time date attachet pr product number.

Today we have used to calculate MEAN to get an average time consumed pr product. We have now learned that MEDIAN would give us more quality data since we sometimes have abnormalities on time consumed.

However. Access 2007 does not seem to have a MEDIAN function, and when i google it i only find code to do this for an entire dataset.

I have 10000 different products in the table, all with 10-50 time consumed rows. I also need to update our MEDIAN time consumed data pr product a couple of times a day. If i should use the code that M$ provides i have to close and open a connection to the table 10000 to get the results that i want

http://support.microsoft.com/kb/q95918/

Anybody have seen another solution for this?

[ADezii]

Subscribing, will return later.

[nico5038]

I found various routines when searching using "access median function".
The only additional thing for you is the filtering of the data for one product, thus you'll have to add the productID to the parameters of the function and the WHERE clause in the strSQL.

Getting the idea ?

Nic;o)

[ADezii]

Here is a little Function that I created that is used within the context of a Calculated Field in a DISTINCT Query. In this particular case, it accepts two Arguments: Distinct Field and Field to calculate the actual Median Value. In this specific instance, I calculated the Median Value for properties by Area. I'll post the SQL, Function Definition, Sample Data, and Query Execution results for clarification.

1. SQL Statement
   

   SELECT DISTINCT tblValues.Area, fCalculateMedian([Area],[Price]) AS Median
   FROM tblValues
   ORDER BY tblValues.Area;

2. Function Definition
   

   Public Function fCalculateMedian(strArea As String, curPrice As Currency)
   Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
   Dim intNumOfRecords As Integer, curPriceValue As Currency
    
   MySQL = "SELECT tblValues.Area, tblValues.Price FROM tblValues "
   MySQL = MySQL & "WHERE tblValues.Area='" & strArea & "' ORDER BY tblValues.Area, tblValues.Price;"
    
   Set MyDB = CurrentDb()
   Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
    
   MyRS.MoveLast: MyRS.MoveFirst
    
   intNumOfRecords = MyRS.RecordCount
    
   If intNumOfRecords = 0 Then
     fCalculateMedian = Null
       Exit Function
   End If
    
   If intNumOfRecords Mod 2 = 0 Then       'Even number of Records
     MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
       curPriceValue = MyRS![Price]        '1st value to average
     MyRS.MoveNext
       curPriceValue = curPriceValue + MyRS![Price]               '2nd value to average added to 1st value
       fCalculateMedian = Format$(curPriceValue / 2, "Currency")  'Average them out
   Else   'Odd number of Records
     MyRS.Move (intNumOfRecords \ 2)
     fCalculateMedian = Format$(MyRS![Price], "Currency")
   End If
    
   MyRS.Close
   End Function

3. Values in tblValues
   

   Area            Price
   California      $100,000.00
   California      $150,000.00
   California      $120,000.00
   New York        $110,000.00
   New York         $20,000.00
   New York        $150,000.00
   New York        $120,000.00
   Philadelphia    $300,000.00
   Philadelphia    $100,000.00
   Philadelphia    $200,000.00
   Philadelphia    $116,000.00
   Philadelphia     $90,000.00
   California       $38,000.00
   Trenton              $0.00

4. Query results
   

   Area              Median
   California        $110,000.00
   New York          $115,000.00
   Philadelphia      $116,000.00
   Trenton                 $0.00

P.S. - There is another alternative if you are interested and that is to let Excel do the work by passing an Array to its MEDIAN() Function.

[MrDeej]

Thank you! That did the trick.

You have solved the challenge :=)