Solved: Median in Access Query

[stanl]

I have just under 3 million rows of mainframe output converted to an Access Table, these consist of 24 readings per day of electric meter CP readings, which are then rolled up to monthly maximum readings per customer, viz

Customer, yyyymmdd, max(CP)

at this point I would like to perform a make-table query and extract both the average and median per customer, then decide which is more useful for further analysis based upon the customer siccode. I have read that you could write an Access function that references the Excel.Worksheet function for median... then I also read that Excel's median function is limited to 30 entries.

This is historical data and runs longer than 30 months, so should I chuck Excel and ask for an alternative:dunno Stan

P.S. the 3 million rows will soon grow to 5 million

[Norie]

Stan

The only way I can see you being able to do this would be using a UDF to create your own median function in Access.

You can use Excel worksheet functions in Access but I think that can be problematic as they expect to be working with ranges.

[stanl]

Stan

The only way I can see you being able to do this would be using a UDF to create your own median function in Access.

You can use Excel worksheet functions in Access but I think that can be problematic as they expect to be working with ranges.

Norie;

this worked as called from Access and I checked it by running it in Excel - ~4000 rows involved {the trick is to send an array to the WorksheetFunction}. The task is to obtain the median for each customer in addition to the entire table


Function getMed()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim arrayNum

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT [NCP] FROM [Cust_History]" & _
" WHERE [NCP] IS NOT NULL")

rs.MoveLast
rs.MoveFirst
arrayNum = rs.GetRows(rs.RecordCount)

getMed = WorksheetFunction.Median(arrayNum)

Set db = Nothing
Set rs = Nothing
MsgBox getMed
End Function


I'm open to suggestions. Stan

[stanl]

Figured it out; just needed an outer recordset to SELECT DISTINCT on Customer Field use this in a WHERE clause - got 216 median summaries in about a second.