How to write code for Median

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:

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?

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];")

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?

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 ...
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

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.

Would the following not work?
Or is my Maths too rusty?

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

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 :(

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

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.

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.