435,286 Members | 2,422 Online
Need help? Post your question and get tips & solutions from a community of 435,286 IT Pros & Developers. It's quick & easy.

# How to write code for Median

 P: 29 I have a textbox that shows Max, Min, Avg, and Median. I have the code for all expect Median. Can somebody please help. Example: For Max, I just went into the properties of the textbox, then in the control source I just put in =Max([Engine Price Est]). What would it be for Median? Feb 27 '07 #1
12 Replies

 Expert Mod 10K+ P: 14,534 This cannot be done with a standard function as there is no way to decide the half way point in a set of numbers you will need a new function like the following: Expand|Select|Wrap|Line Numbers Public Function findMedianPrice() As Double Dim db As DAO.Database Dim rs As DAO.Recordset Dim recCount As Integer Dim tmpMed as Double      Set db = CurrentDb    Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM TableName " & _           "ORDER BY [Engine Price Est];"      rs.MoveLast    rs.MoveFirst    recCount = rs.RecordCount      If recCount mod 2 = 0 Then ' If even number of records       rs.Move recCount/2       tmpMed = rs![Engine Price Est]       rs.MoveNext       tmpMed = tmpMed + rs![Engine Price Est]       tmpMed = tmpMed/2    Else       rs.Move (recCount -1)/2       rs.MoveNext       tmpMed = rs![Engine Price Est]    End If      findMedianPrice = tmpMed   End Sub   Feb 27 '07 #2

 P: 29 This cannot be done with a standard function as there is no way to decide the half way point in a set of numbers you will need a new function like the following: Expand|Select|Wrap|Line Numbers Public Function findMedianPrice() As Double Dim db As DAO.Database Dim rs As DAO.Recordset Dim recCount As Integer Dim tmpMed as Double      Set db = CurrentDb    Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM TableName " & _           "ORDER BY [Engine Price Est];"      rs.MoveLast    rs.MoveFirst    recCount = rs.RecordCount      If recCount mod 2 = 0 Then ' If even number of records       rs.Move recCount/2       tmpMed = rs![Engine Price Est]       rs.MoveNext       tmpMed = tmpMed + rs![Engine Price Est]       tmpMed = tmpMed/2    Else       rs.Move (recCount -1)/2       rs.MoveNext       tmpMed = rs![Engine Price Est]    End If      findMedianPrice = tmpMed   End Sub   Your code: Dim db As DAO.Database Dim rs As DAO.Recordset Does DAO only work with the newer versions of Access? What if I was doing this in Access 97? Would it still work? I put in the code you gave me and I got a line of code that it didn't like. The line of code that was shown in red is: Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM All Areas with additions " & "ORDER BY [Engine Price Est];" I don't know what the problem is? Can you help? Feb 27 '07 #3

 Expert Mod 10K+ P: 14,534 Your code: Dim db As DAO.Database Dim rs As DAO.Recordset Does DAO only work with the newer versions of Access? What if I was doing this in Access 97? Would it still work? I put in the code you gave me and I got a line of code that it didn't like. The line of code that was shown in red is: Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM All Areas with additions " & "ORDER BY [Engine Price Est];" I don't know what the problem is? Can you help? Go to Tools - References in the VBA Editor window and make sure that the Microsoft DAO library is ticked. Change code to ... Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM [All Areas with additions] " & _ "ORDER BY [Engine Price Est];") Feb 27 '07 #4

 P: 29 Go to Tools - References in the VBA Editor window and make sure that the Microsoft DAO library is ticked. Change code to ... Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM [All Areas with additions] " & _ "ORDER BY [Engine Price Est];") The code you gave me is for a report that is linked to a form (a form that lets people build queries) then prints out a report of the query. So with this being said, I want the median of only the query (over group) not over the whole table. What would the VBA code be for this? It wouldn't be the same would it? Feb 27 '07 #5

 Expert Mod 10K+ P: 14,534 The code you gave me is for a report that is linked to a form (a form that lets people build queries) then prints out a report of the query. So with this being said, I want the median of only the query (over group) not over the whole table. What would the VBA code be for this? It wouldn't be the same would it? Dynamic reporting is a b***. Sorry just had to get that out of the way. The problem is how to put the subset into the recordset: This ... Expand|Select|Wrap|Line Numbers   Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM [All Areas with additions] " & _           "ORDER BY [Engine Price Est];") Would have to be changed to only reflect the subset of data you are working from. Without knowing a lot more I can't really help. I don't think it will be easy though. Mary Feb 27 '07 #6

 P: 29 Dynamic reporting is a b***. Sorry just had to get that out of the way. The problem is how to put the subset into the recordset: This ... Expand|Select|Wrap|Line Numbers   Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM [All Areas with additions] " & _           "ORDER BY [Engine Price Est];") Would have to be changed to only reflect the subset of data you are working from. Without knowing a lot more I can't really help. I don't think it will be easy though. Mary Thanks, for all your help. I'll see what I can do. Feb 27 '07 #7

 Expert Mod 15k+ P: 31,494 Would the following not work? Expand|Select|Wrap|Line Numbers =(Max([Engine Price Est])+Min([Engine Price Est]))/2 Or is my Maths too rusty? Mar 2 '07 #8

 Expert Mod 10K+ P: 14,534 Would the following not work? Expand|Select|Wrap|Line Numbers =(Max([Engine Price Est])+Min([Engine Price Est]))/2 Or is my Maths too rusty? The median of a set of numbers is nothing to do with half the value of the numbers. It is the one (odd set) or half the value of the two (even set) number(s) that occur half way through the ordered set. Mary Mar 2 '07 #9

 Expert Mod 15k+ P: 31,494 Would the following not work? Expand|Select|Wrap|Line Numbers =(Max([Engine Price Est])+Min([Engine Price Est]))/2 Or is my Maths too rusty? The median of a set of numbers is nothing to do with half the value of the numbers. It is the one (odd set) or half the value of the two (even set) number(s) that occur half way through the ordered set. Mary The latter then :( Mar 2 '07 #10

 Expert Mod 10K+ P: 14,534 The latter then :( I sound so knowledgible don't I. Actually I couldn't be sure of my memory on the issue so I looked it up. Mary Mar 2 '07 #11

 Expert Mod 15k+ P: 31,494 The code you gave me is for a report that is linked to a form (a form that lets people build queries) then prints out a report of the query. So with this being said, I want the median of only the query (over group) not over the whole table. What would the VBA code be for this? It wouldn't be the same would it? This is a challenge! I will write a Public Function that can be called from within a query. When passed one of the values of the grouped field (I will assume the field name [Group] unless told otherwise) it will return the Median value for that particular group. As it will build up the values in an internal array during the first call, the efficiency will be optimum for calling within a query as the hard work only needs to be executed once. Mar 3 '07 #12