449,050 Members | 1,295 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,050 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
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

 Expert 5K+ P: 8,675 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 Aug 26 '08 #3

 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

 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

 Expert 5K+ P: 8,675 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 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 Aug 27 '08 #7

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

 P: 22 Try: Expand|Select|Wrap|Line Numbers 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! Aug 27 '08 #9

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