bng0005
asked on
Getting Median value...
Hey experts,
Been looking around on how to go about this but haven't had any luck so far. I am trying to find the median value of prices on one of my subforms, but unfortunately, there's no handy function like Avg to do this. Is there a similar function in Access that will return the median value of a field in my recordset that I don't know about? If not, is there a way to create a function that would do basically the same thing?
And not to insult your intelligence, just to save searching time on finding median
Median = middle number in sorted list if odd # in list
or middle 2 numbers / 2 in an even # list
Thanks for any input.
Bryan
Been looking around on how to go about this but haven't had any luck so far. I am trying to find the median value of prices on one of my subforms, but unfortunately, there's no handy function like Avg to do this. Is there a similar function in Access that will return the median value of a field in my recordset that I don't know about? If not, is there a way to create a function that would do basically the same thing?
And not to insult your intelligence, just to save searching time on finding median
Median = middle number in sorted list if odd # in list
or middle 2 numbers / 2 in an even # list
Thanks for any input.
Bryan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nice Pete!
No credit to me... I just copied them from somewhere else.
Pete
Pete
ASKER
Ok I got it partially working from the code in the link flavo posted. I changed it a bit to only return the median for the recordset I'm currently viewing based off a field in my subform.
Now here's where it gets interesting. My parent form consists of 2 comboboxes to filter the subform recordsource. I want to be able to calculate the median off of either combobox selection. Right now, all I did was change the code to include WHERE CommunityName = cboCommName, but if the user were to sort by Zip Code, the median field would be wrong.
Right now, here is the code I'm using to select the records
Private Sub cboZipCode_AfterUpdate()
Dim sqlString As String
sqlString = "SELECT * FROM MLSDataTbl WHERE ZipCode = " & cboZipCode.value & ""
Me.subDataFrm.Form.RecordS
Me.subDataFrm.Requery
End Sub
and I just change cboZipCode to cboCommName for my other combobox.
If none of that made any sense let me know. Kinda confused myself trying to explain it heh.
Bryan
Now here's where it gets interesting. My parent form consists of 2 comboboxes to filter the subform recordsource. I want to be able to calculate the median off of either combobox selection. Right now, all I did was change the code to include WHERE CommunityName = cboCommName, but if the user were to sort by Zip Code, the median field would be wrong.
Right now, here is the code I'm using to select the records
Private Sub cboZipCode_AfterUpdate()
Dim sqlString As String
sqlString = "SELECT * FROM MLSDataTbl WHERE ZipCode = " & cboZipCode.value & ""
Me.subDataFrm.Form.RecordS
Me.subDataFrm.Requery
End Sub
and I just change cboZipCode to cboCommName for my other combobox.
If none of that made any sense let me know. Kinda confused myself trying to explain it heh.
Bryan
ASKER
figured out a way that worked decently, thanks again for the help.
Bryan
Bryan
Got these from Google....
SELECT d1.c AS median
FROM dist AS d1, dist AS d2
GROUP BY d1.c
HAVING
COUNT(IIf(d2.c <= d1.c,1,null))=(COUNT(*)+1)
gives the lesser of the two middle values if there is an even number
SELECT IIf((COUNT(*) Mod 2)=0,
(d1.c+MIN(IIf(d2.c>d1.c,d2
AS median
FROM dist AS d1, dist AS d2
GROUP BY d1.c
HAVING
COUNT(IIf(d2.c <= d1.c,1,null))=(COUNT(*)+1)
this one gives the average of two middle values if there is an even number
Pete