Median of Different Field Groups

[denveromlp]

Hello,

I have a table of variables (X1, X2, X3...) that each have multiple measurements (Y1.1, Y1.2, Y1.3, Y2.1...)

Variable Measurement
X1 Y1.1
X1 Y1.2
X1 Y1.3
X1 Y1.4
X2 Y2.1
X2 Y2.2
X2 Y2.3
X3 Y3.1
X3 Y3.2
X3 Y3.3

I ned to find the median Y within each X group. I've used the "avg" from the total drop down before but it doesn't look like there is a selection for median.

Is there a way to find these medians?
Thanks

[Stewart Ross]

Hi. Simplest way is not automatic. Group the data using a Group By (Totals) query on X and Y, and sort in order on both fields. The median is the middle value of each ordered set (or the average of the two mid values if there are an even number of values in the set).

If you need to use a function to find the median this MS knowlede base article provides the code for doing so. You would need to feed it with a query which restricts the grouping to one set of Y data at a time (perhaps using a subquery to do this), as the function is not set up to find multiple medians simultaneously.

-Stewart

[ADezii]

These Links may be helpful to you.
http://bytes.com/forum/thread655107.html
http://bytes.com/forum/thread608740.html
http://support.microsoft.com/kb/210581

[denveromlp]

OK, thanks for your help, I've got the module running, but it is returning incorrect values. In fact, the values it is returning as medians, are not even values included in the table. See any mistakes?
-Nate

Public Function fCalculateMedian(strfleetname As String, dblmotorMWHRs As Double)
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
Dim intNumOfRecords As Integer, curMotorMWHR As Double

MySQL = "SELECT tbl_MWHRSday.FLEET_NAME, tbl_MWHRSday.avg_mwhrs FROM tbl_MWHRSday "
MySQL = MySQL & "WHERE tbl_MWHRSday.Fleet_Name='" & strfleetname & "' ORDER BY tbl_MWHRSday.Fleet_Name, tbl_MWHRSday.avg_mwhrs;"

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)

MyRS.MoveLast: MyRS.MoveFirst

intNumOfRecords = MyRS.RecordCount
If intNumOfRecords = 0 Then Exit Function

If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
dblmotorMWHRs = MyRS![avg_mwhrs] '1st value to average
MyRS.MoveNext
curMotorMWHR = curMotorMWHR + MyRS![avg_mwhrs] '2nd value to average added to 1st value
fCalculateMedian = curMotorMWHR / 2 'Average them out
Else 'Odd number of Records
MyRS.Move (intNumOfRecords \ 2)
fCalculateMedian = MyRS![avg_mwhrs]
End If

MyRS.Close
End Function

[FishVal]

Looks more or less ok, but what for do you pass dblmotorMWHRs argument to the function?

Kind regards,
Fish

P.S. Though not sure, I think you may encounter a problem using the function in query. Query may lock your table to open it with DAO. If it happens, then try to use ADO instead.

[denveromlp]

Looks more or less ok, but what for do you pass dblmotorMWHRs argument to the function?

Kind regards,
Fish

P.S. Though not sure, I think you may encounter a problem using the function in query. Query may lock your table to open it with DAO. If it happens, then try to use ADO instead.

I'm not sure wha the purpose is, I based the code from http://bytes.com/forum/thread655107.html

If I don't include it I get and error "Argument count mismatch"

[ADezii]

OK, thanks for your help, I've got the module running, but it is returning incorrect values. In fact, the values it is returning as medians, are not even values included in the table. See any mistakes?
-Nate

Public Function fCalculateMedian(strfleetname As String, dblmotorMWHRs As Double)
Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
Dim intNumOfRecords As Integer, curMotorMWHR As Double

MySQL = "SELECT tbl_MWHRSday.FLEET_NAME, tbl_MWHRSday.avg_mwhrs FROM tbl_MWHRSday "
MySQL = MySQL & "WHERE tbl_MWHRSday.Fleet_Name='" & strfleetname & "' ORDER BY tbl_MWHRSday.Fleet_Name, tbl_MWHRSday.avg_mwhrs;"

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)

MyRS.MoveLast: MyRS.MoveFirst

intNumOfRecords = MyRS.RecordCount
If intNumOfRecords = 0 Then Exit Function

If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
dblmotorMWHRs = MyRS![avg_mwhrs] '1st value to average
MyRS.MoveNext
curMotorMWHR = curMotorMWHR + MyRS![avg_mwhrs] '2nd value to average added to 1st value
fCalculateMedian = curMotorMWHR / 2 'Average them out
Else 'Odd number of Records
MyRS.Move (intNumOfRecords \ 2)
fCalculateMedian = MyRS![avg_mwhrs]
End If

MyRS.Close
End Function

Try:

If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
  MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
  dblmotorMWHRs = MyRS![avg_mwhrs] '1st value to average
    MyRS.MoveNext
  dblmotorMWHRs = dblmotorMWHRs + MyRS![avg_mwhrs] '2nd value to average added to 1st value
    fCalculateMedian = dblmotorMWHRs / 2 'Average them out
Else 'Odd number of Records
  MyRS.Move (intNumOfRecords \ 2)
  fCalculateMedian = MyRS![avg_mwhrs]
End If

[FishVal]

MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
 

should be

MyRS.Move (intNumOfRecords \ 2 - 1)   'Move half-way point
 

[denveromlp]

Try:

If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
  MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
  dblmotorMWHRs = MyRS![avg_mwhrs] '1st value to average
    MyRS.MoveNext
  dblmotorMWHRs = dblmotorMWHRs + MyRS![avg_mwhrs] '2nd value to average added to 1st value
    fCalculateMedian = dblmotorMWHRs / 2 'Average them out
Else 'Odd number of Records
  MyRS.Move (intNumOfRecords \ 2)
  fCalculateMedian = MyRS![avg_mwhrs]
End If

Yup, that's it, just caught it myself.
Thanks for your help!

[ADezii]

Yup, that's it, just caught it myself.
Thanks for your help!

You are quite welcome, denveromlp.