By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,286 Members | 2,422 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
12 Replies


MMcCarthy
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
  1. Public Function findMedianPrice() As Double
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim recCount As Integer
  5. Dim tmpMed as Double
  6.  
  7.    Set db = CurrentDb
  8.    Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM TableName " & _
  9.           "ORDER BY [Engine Price Est];"
  10.  
  11.    rs.MoveLast
  12.    rs.MoveFirst
  13.    recCount = rs.RecordCount
  14.  
  15.    If recCount mod 2 = 0 Then ' If even number of records
  16.       rs.Move recCount/2
  17.       tmpMed = rs![Engine Price Est]
  18.       rs.MoveNext
  19.       tmpMed = tmpMed + rs![Engine Price Est]
  20.       tmpMed = tmpMed/2
  21.    Else
  22.       rs.Move (recCount -1)/2
  23.       rs.MoveNext
  24.       tmpMed = rs![Engine Price Est]
  25.    End If
  26.  
  27.    findMedianPrice = tmpMed
  28.  
  29. End Sub
  30.  
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
  1. Public Function findMedianPrice() As Double
  2. Dim db As DAO.Database
  3. Dim rs As DAO.Recordset
  4. Dim recCount As Integer
  5. Dim tmpMed as Double
  6.  
  7.    Set db = CurrentDb
  8.    Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM TableName " & _
  9.           "ORDER BY [Engine Price Est];"
  10.  
  11.    rs.MoveLast
  12.    rs.MoveFirst
  13.    recCount = rs.RecordCount
  14.  
  15.    If recCount mod 2 = 0 Then ' If even number of records
  16.       rs.Move recCount/2
  17.       tmpMed = rs![Engine Price Est]
  18.       rs.MoveNext
  19.       tmpMed = tmpMed + rs![Engine Price Est]
  20.       tmpMed = tmpMed/2
  21.    Else
  22.       rs.Move (recCount -1)/2
  23.       rs.MoveNext
  24.       tmpMed = rs![Engine Price Est]
  25.    End If
  26.  
  27.    findMedianPrice = tmpMed
  28.  
  29. End Sub
  30.  
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

MMcCarthy
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

MMcCarthy
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
  1.  
  2. Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM [All Areas with additions] " & _
  3.           "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
  1.  
  2. Set rs = db.OpenRecordset("SELECT [Engine Price Est] FROM [All Areas with additions] " & _
  3.           "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

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

MMcCarthy
Expert Mod 10K+
P: 14,534
Would the following not work?
Expand|Select|Wrap|Line Numbers
  1. =(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

NeoPa
Expert Mod 15k+
P: 31,494
Would the following not work?
Expand|Select|Wrap|Line Numbers
  1. =(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

MMcCarthy
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

NeoPa
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

NeoPa
Expert Mod 15k+
P: 31,494
Here's the code.
It can be added to an existing module (Not Class Module) or one can be created for it to go in. If adding to an existing module the two top lines should already be there so can be dropped.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Function MedianPrice(strGroup As String) As Double
  5.   Static astrGrps() As String
  6.   Static adblMedians() As Double
  7.   Dim strGrp As String, strSQL As String
  8.   Dim intGrp As Integer, intGrps As Integer
  9.   Dim intStart As Integer, intRec As Integer
  10.  
  11.   'If UBound() fails then intGrps stays as 0 ==> first call
  12.   On Error Resume Next
  13.   intGrps = UBound(astrGrps)
  14.   On Error GoTo 0
  15.   If intGrps = 0 Then
  16.     'First time through only
  17.     strSQL = "SELECT DISTINCT [Group] " & _
  18.              "FROM [All Areas with additions]"
  19.     intGrps = DCount("[Group]", strSQL)
  20.     ReDim astrGrps(1 To intGrps)
  21.     ReDim adblMedians(1 To intGrps)
  22.     strSQL = "SELECT [Group],[Engine Price Est] " & _
  23.              "FROM [All Areas with additions] " & _
  24.              "ORDER BY [Group],[Engine Price Est]"
  25.     With CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)
  26.       Call .MoveFirst
  27.       strGrp = ![Group]
  28.       intStart = 1
  29.       intRec = 1
  30.       For intGrp = 1 To intGrps
  31.         While ((Not .EOF) And (![Group] = strGrp))
  32.           intRec = intRec + 1
  33.           Call .MoveNext
  34.         Wend
  35.         astrGrps(intGrp) = strGrp
  36.         strGrp = ![Group]
  37.         'Go back to middle record or first of two middle records
  38.         Call .Move(Rows:=Fix((intStart - intRec) / 2) - 1)
  39.         adblMedians(intGrp) = ![Engine Price Est]
  40.         'If two middle records process the average
  41.         If ((intRec - intStart) Mod 2) = 0 Then
  42.           Call .MoveNext
  43.           adblMedians(intGrp) = (adblMedians(intGrp) + ![Engine Price Est]) / 2
  44.         End If
  45.         'Return to record intRec
  46.         Call .Move(Rows:=Fix((intRec + 1 - intStart) / 2))
  47.       Next intGrp
  48.     End With
  49.   End If
  50.  
  51.   'Find the index of strGroup & return matching value
  52.   For intGrp = 1 To UBound(astrGrps)
  53.     If strGroup = astrGrps(intGrp) Then Exit For
  54.   Next intGrp
  55.   MedianPrice = adblMedians(intGrp)
  56. End Function
Mar 3 '07 #13

Post your reply

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