P: 22

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
 
Share this Question
Expert Mod 2.5K+
P: 2,545

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
 
P: 22

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 halfway 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
  Expert 2.5K+
P: 2,653

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.
 
P: 22

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"
  Expert 5K+
P: 8,619

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 halfway 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 halfway 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
  Expert 2.5K+
P: 2,653
 
MyRS.Move (intNumOfRecords \ 2)  1 'Move halfway point

should be 
MyRS.Move (intNumOfRecords \ 2  1) 'Move halfway point

 
P: 22
 Try:  If intNumOfRecords Mod 2 = 0 Then 'Even number of Records

MyRS.Move (intNumOfRecords \ 2)  1 'Move halfway 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!
  Expert 5K+
P: 8,619

Yup, that's it, just caught it myself.
Thanks for your help!
You are quite welcome, denveromlp.
    Question stats  viewed: 1294
 replies: 9
 date asked: Aug 26 '08
