Mass calculate MEDIAN of large data set

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?

Subscribing, will return later.

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)

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
   
   Expand|Select|Wrap|Line Numbers

   1. SELECT DISTINCT tblValues.Area, fCalculateMedian([Area],[Price]) AS Median
   2. FROM tblValues
   3. ORDER BY tblValues.Area;
2. Function Definition
   
   Expand|Select|Wrap|Line Numbers

   1. Public Function fCalculateMedian(strArea As String, curPrice As Currency)
   2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
   3. Dim intNumOfRecords As Integer, curPriceValue As Currency
   4.  
   5. MySQL = "SELECT tblValues.Area, tblValues.Price FROM tblValues "
   6. MySQL = MySQL & "WHERE tblValues.Area='" & strArea & "' ORDER BY tblValues.Area, tblValues.Price;"
   7.  
   8. Set MyDB = CurrentDb()
   9. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
   10.  
   11. MyRS.MoveLast: MyRS.MoveFirst
   12.  
   13. intNumOfRecords = MyRS.RecordCount
   14.  
   15. If intNumOfRecords = 0 Then
   16.   fCalculateMedian = Null
   17.     Exit Function
   18. End If
   19.  
   20. If intNumOfRecords Mod 2 = 0 Then       'Even number of Records
   21.   MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
   22.     curPriceValue = MyRS![Price]        '1st value to average
   23.   MyRS.MoveNext
   24.     curPriceValue = curPriceValue + MyRS![Price]               '2nd value to average added to 1st value
   25.     fCalculateMedian = Format$(curPriceValue / 2, "Currency")  'Average them out
   26. Else   'Odd number of Records
   27.   MyRS.Move (intNumOfRecords \ 2)
   28.   fCalculateMedian = Format$(MyRS![Price], "Currency")
   29. End If
   30.  
   31. MyRS.Close
   32. End Function
3. Values in tblValues
   
   Expand|Select|Wrap|Line Numbers

   1. Area            Price
   2. California      $100,000.00
   3. California      $150,000.00
   4. California      $120,000.00
   5. New York        $110,000.00
   6. New York         $20,000.00
   7. New York        $150,000.00
   8. New York        $120,000.00
   9. Philadelphia    $300,000.00
   10. Philadelphia    $100,000.00
   11. Philadelphia    $200,000.00
   12. Philadelphia    $116,000.00
   13. Philadelphia     $90,000.00
   14. California       $38,000.00
   15. Trenton              $0.00
4. Query results
   
   Expand|Select|Wrap|Line Numbers

   1. Area              Median
   2. California        $110,000.00
   3. New York          $115,000.00
   4. Philadelphia      $116,000.00
   5. 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.

Thank you! That did the trick.

You have solved the challenge :=)