By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,956 Members | 1,722 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,956 IT Pros & Developers. It's quick & easy.

Median of Different Field Groups

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
Aug 26 '08 #1
Share this Question
Share on Google+
9 Replies


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
Aug 26 '08 #2

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 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
Aug 27 '08 #4

FishVal
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.
Aug 27 '08 #5

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"
Aug 27 '08 #6

ADezii
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 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:
Expand|Select|Wrap|Line Numbers
  1. If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
  2.   MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
  3.   dblmotorMWHRs = MyRS![avg_mwhrs] '1st value to average
  4.     MyRS.MoveNext
  5.   dblmotorMWHRs = dblmotorMWHRs + MyRS![avg_mwhrs] '2nd value to average added to 1st value
  6.     fCalculateMedian = dblmotorMWHRs / 2 'Average them out
  7. Else 'Odd number of Records
  8.   MyRS.Move (intNumOfRecords \ 2)
  9.   fCalculateMedian = MyRS![avg_mwhrs]
  10. End If
Aug 27 '08 #7

FishVal
Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
  2.  
should be
Expand|Select|Wrap|Line Numbers
  1. MyRS.Move (intNumOfRecords \ 2 - 1)   'Move half-way point
  2.  
Aug 27 '08 #8

P: 22
Try:
Expand|Select|Wrap|Line Numbers
  1. If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
  2.   MyRS.Move (intNumOfRecords \ 2) - 1 'Move half-way point
  3.   dblmotorMWHRs = MyRS![avg_mwhrs] '1st value to average
  4.     MyRS.MoveNext
  5.   dblmotorMWHRs = dblmotorMWHRs + MyRS![avg_mwhrs] '2nd value to average added to 1st value
  6.     fCalculateMedian = dblmotorMWHRs / 2 'Average them out
  7. Else 'Odd number of Records
  8.   MyRS.Move (intNumOfRecords \ 2)
  9.   fCalculateMedian = MyRS![avg_mwhrs]
  10. End If
Yup, that's it, just caught it myself.
Thanks for your help!
Aug 27 '08 #9

ADezii
Expert 5K+
P: 8,619
Yup, that's it, just caught it myself.
Thanks for your help!
You are quite welcome, denveromlp.
Aug 27 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.