Calculate Median in Access Query

[kjo1080]

Does anyone know how to calculate the median in an Access Query? there is a formula for average but not for median. Does anyone know of a way to do this or of how to write a formula in a module that will work?

Any help is appreciated!

Thanks,

kjo1080

 

[funkykizzy]

Hi again, Sydney!

I tried to make two tables per your suggestion in the referenced thread above but I am having difficulty getting the identifyier of one table copied as a reference in the other table. I am trying to accomplish this using an Update Query with the following SQL:

UPDATE AVERAGED, ANALYTE SET AVERAGED.ANALYTEID = DLookUp("ANALYTEID","ANALYTE","ANALYTE =" & [ANALYTE]![ANALYTE]);

Where AVERAGED and ANALYTE are the two tables. Both of which contain fields called ANALYTE and ANALYTEID. I am trying to get the field ANALYTEID in the AVERAGED table to match ANALYTEID in the ANALYTE table wherever the values of the field ANALYTE match in both tables. Once I accomplish this I plan on having the tables joined by ANALYTEID.

Unfortunately, when I try to run this query I get:

Operation must use an updatable query. (Error 3073)

I tried elliminating all table relationships, checking the table and query properties, and restarting the program but nothing seems to work.

Sorry to be so needy, but does anyone have any suggestions? Thank you so much!

 

[xenou]

UPDATE [COLOR="Red"]AVERAGED, ANALYTE[/COLOR] SET AVERAGED.ANALYTEID = DLookUp("ANALYTEID","ANALYTE","ANALYTE =" & [ANALYTE]![ANALYTE]);

This looks odd with two tables in the update. Try updating only one table:

UPDATE [COLOR="Red"]AVERAGED[/COLOR] SET AVERAGED.ANALYTEID = DLookUp("ANALYTEID","ANALYTE","ANALYTE =" & [ANALYTE]![ANALYTE]);

It's not clear why you need the update if the Analyte field and the AnalyteID field are already interchangeable (i.e., knowing one means knowing the other).

 

[funkykizzy]

Hi again!

I have reworked my approach to this problem by calling the Excel MEDIAN function with an Access module.

Public Function excel_median()
    Dim rs As New ADODB.Recordset
    Dim xcl As Object
    Set xcl = CreateObject("Excel.Application")
    rs.Open "Select * from AVERAGED", CurrentProject.Connection, _
        adOpenKeyset, adLockOptimistic
    Do Until rs.EOF
        rs.Fields("Median") = _
            xcl.WorksheetFunction.Median(rs.Fields("AvgOfRESULT-NUMBER"))
    rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing
    Set xcl = Nothing
    MsgBox "done"
End Function

The problem is that I have this called in a Make Table query and so the "Median" field is not yet active and I get RunTime Error 3265 at the line:

rs.Fields("Median") = _
            xcl.WorksheetFunction.Median(rs.Fields("AvgOfRESULT-NUMBER"))

I have been trying to work around this with no luck. Is there a way to reference the next new field as an object variable and then name that variable in the Do Until loop?

For reference, here is my query SQL:

SELECT AVERAGED.METHODCODE, AVERAGED.[PM TYPE], AVERAGED.ANALYTE, Min(AVERAGED.[AvgOfRESULT-NUMBER]) AS [MinOfAvgOfRESULT-NUMBER], Max(AVERAGED.[AvgOfRESULT-NUMBER]) AS [MaxOfAvgOfRESULT-NUMBER], Avg(AVERAGED.[AvgOfRESULT-NUMBER]) AS [AvgOfAvgOfRESULT-NUMBER], StDev(AVERAGED.[AvgOfRESULT-NUMBER]) AS [StDevOfAvgOfRESULT-NUMBER], excel_median() AS Median INTO Table1
FROM AVERAGED
GROUP BY AVERAGED.METHODCODE, AVERAGED.[PM TYPE], AVERAGED.ANALYTE, excel_median()
ORDER BY AVERAGED.METHODCODE, AVERAGED.[PM TYPE], AVERAGED.ANALYTE;

In my example, the data to be calculated is in the "AvgOfRESULT-NUMBER" field.

Thank you all so much in advance!!

 

[xenou]

rs.Fields("Median") = _
xcl.WorksheetFunction.Median(rs.Fields("AvgOfRESULT-NUMBER"))

a rs.Fields("x") value will be only the value of the field for the current row - this won't work as it's only one value (or more precisely, the result won't be what you want as it will be the median of a single value only, not the entire column of values).

I suppose you could create a median function by querying for the value in ascending order, then use a seek method on the recordset to get to the record in the middle (you will need to decide how to handle cases where there is an even number of records and therefore two "middle" values). Surely there must be someone out there who's solved this problem already (getting a median value from an Access field)... [Edit: ah, see post #1 ... but the code in that MS link needs to be tweaked, I think, as we shouldn't need to move through the records one at a time to get to the middle. Giacomo's posts show a better approach on this]

 

[funkykizzy]

Hi Xenou!

Thanks! Unfortunately, I need to run other Excel functions on the data in addition to median (the median is only the first one that I am attempting). You're right, someone out there should have figured out how to apply Excel stats calculations to Access fields and created a summary table from this information...I just haven't found them yet .

I will try to rework with Giacomo's approach again...

Thanks! Have a Happy Thanksgiving!

 

[xenou]

Not sure. There's some functions to try at the beginning of this thread. I suppose there's better statistical analysis tools than Access (i.e., minitab and its kind). If you have Excel 2007+ you can work with larger datasets in Excel now too.

Edit - keep posting your progress and questions. The more information about what you've got here the better.
Happy Thanksgiving also.

 

[SydneyGeek]

If you want to do a bunch of statistics in Access here's a couple of options:

1. Create a reference to Excel and call the functions from Access. See... http://support.microsoft.com/kb/198571

2. Look at Mike Alexander's Access Data Analysis book

3. Purchase the Statistics add-in from FMS

Denis

 

[funkykizzy]

Thank you for your suggestions, they are very much appreciated!

My company will be upgrading to 2007 in 3 weeks, unfortunately I am required to solve this issue by the end of business tomorrow. Moreover, I am not in a position to purchase the FMS add-in with this project budget. I followed up on all of the suggestions posted to this forum but I still seem to run into errors.

I understand that my VBA code is calculating each line individually, how do I get it to evaluate the recordset as an Excel argument when it is grouped? It seems like I could achieve my goal if I could just figure that out...

Thanks again!!

 

[xenou]

I understand that my VBA code is calculating each line individually, how do I get it to evaluate the recordset as an Excel argument when it is grouped? It seems like I could achieve my goal if I could just figure that out...

You need to do the math or run a process on the records yourself - essentially, creating your own functions. The example in the first posts in this thread show an example of how to do this to get a median value from a recordset.

If you are short on time - push it out to excel and use Excel's statistical analysis functions.

 

[funkykizzy]

Thanks Xenou!

Pushing the data to Excel and performing the analysis there was my plan B...I think I will try that to save time as I am more comfortable with Excel VBA at the moment.

Thanks again!! I really value the effort that you all put into helping others on this forum. Much obliged.