Median Value Question (Different from previous ones)

Hi ppl,

I'm very new to coding in Access and my task now involves finding median values for all Areas, given a table with "Area", "Units Sold" and "Price"

Example:
"Area" "Units Sold" "Price"
California 2 $100,000
California 5 $120,000
California 3 $150,000
New York 7 $70,000
New York 5 $110,000
New York 2 $150,000

I'm trying to find a way to return the following query
"Area" "Median Price"
California $120,000
New York $90,000 (which is $70,000+$110,000 / 2)

Saw some codes which did not have the "Units Sold" field, and my life is complicated by it. Any help would be greatly appreciated.

Thanks,
Ronald

I don't get why New York is $90,000 as there are three records.

How is Units Sold affecting the Median?

Do you want to find the Median of this ....

"Area" "Price"
California $100,000
California $100,000
California $120,000
California $120,000
California $120,000
California $120,000
California $120,000
California $150,000
California $150,000
California $150,000

I don't get why New York is $90,000 as there are three records.

How is Units Sold affecting the Median?

Do you want to find the Median of this ....

"Area" "Price"
California $100,000
California $100,000
California $120,000
California $120,000
California $120,000
California $120,000
California $120,000
California $150,000
California $150,000
California $150,000

[quote=mmccarthy]I don't get why New York is $90,000 as there are three records.

Hi Mmccarthy, thanks for replying. I saw your post (and Neo's) on a previous find the Median issue. Yes you are right, the "Area" and "Price" for CA you came up with is exactly what I am trying to find, EXCEPT that my data comes in the form which I had typed out earlier (condensed with a Number of Units beside the Price and not expanded out fully).

I know that the previous post had the fully expanded prices but my data doesn't come in that manner and i'm limited by that. So by now, I sure you understand that though there are only 3 records for NY, there are actually close 14 units(i forgot) and the Median is the Price of the 7th (70k) and 8th units (110k) divided by 2, giving us 90k.

Working a little more on my database, I managed to get a column up on the extreme right which has the total number of units for NY and CA so the new table that I have to work with (should make life easier) is

Area Units Price TotalforArea
NY 7 70k 14
NY 5 110k 14
NY 2 130k 14

Thanks, I know I probably have to open a recordset and do a count, but I'm like totally new to Access VBA....Hope to hear your reply!

Ronald

Hi ppl,

I'm very new to coding in Access and my task now involves finding median values for all Areas, given a table with "Area", "Units Sold" and "Price"

Example:
"Area" "Units Sold" "Price"
California 2 $100,000
California 5 $120,000
California 3 $150,000
New York 7 $70,000
New York 5 $110,000
New York 2 $150,000

I'm trying to find a way to return the following query
"Area" "Median Price"
California $120,000
New York $90,000 (which is $70,000+$110,000 / 2)

Saw some codes which did not have the "Units Sold" field, and my life is complicated by it. Any help would be greatly appreciated.

Thanks,
Ronald

Assumptions:

1. Your Table Name is tblValues.
2. tblValues contains the Fields [Area] (TEXT), [Units Sold] (LONG), [Price] (CURRENCY).

Procedure:

1. Create a New Query, add tblValues to it, then transfer the SQL Statement below to the SQL View Window. Access will do the rest automatically.
   
   Expand|Select|Wrap|Line Numbers

   1. SELECT DISTINCT tblValues.Area, fCalculateMedian([Area],[Price]) AS Median
   2. FROM tblValues
   3. ORDER BY tblValues.Area;
2. Copy the Function Code below to a Standard Code Module.
   
   Expand|Select|Wrap|Line Numbers

   1. Public Function fCalculateMedian(strArea As String, curPrice As Currency)
   2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
   3. Dim intNumOfRecords As Integer, curPriceValue As Currency
   4.  
   5. MySQL = "SELECT tblValues.Area, tblValues.Price FROM tblValues "
   6. MySQL = MySQL & "WHERE tblValues.Area='" & strArea & "' ORDER BY tblValues.Area, tblValues.Price;"
   7.  
   8. Set MyDB = CurrentDb()
   9. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
   10.  
   11. MyRS.MoveLast: MyRS.MoveFirst
   12.  
   13. intNumOfRecords = MyRS.RecordCount
   14. If intNumOfRecords = 0 Then Exit Function
   15.  
   16. If intNumOfRecords Mod 2 = 0 Then     'Even number of Records
   17.   MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
   18.     curPriceValue = MyRS![Price]      '1st value to average
   19.   MyRS.MoveNext
   20.     curPriceValue = curPriceValue + MyRS![Price]               '2nd value to average added to 1st value
   21.     fCalculateMedian = Format$(curPriceValue / 2, "Currency")  'Average them out
   22. Else   'Odd number of Records
   23.   MyRS.Move (intNumOfRecords \ 2)
   24.   fCalculateMedian = Format$(MyRS![Price], "Currency")
   25. End If
   26.  
   27. MyRS.Close
   28. End Function
3. Sample Output is listed below, if you have any problems, let us know.

OUTPUT:

Hi ppl,

I'm very new to coding in Access and my task now involves finding median values for all Areas, given a table with "Area", "Units Sold" and "Price"

Example:
"Area" "Units Sold" "Price"
California 2 $100,000
California 5 $120,000
California 3 $150,000
New York 7 $70,000
New York 5 $110,000
New York 2 $150,000

I'm trying to find a way to return the following query
"Area" "Median Price"
California $120,000
New York $90,000 (which is $70,000+$110,000 / 2)

Saw some codes which did not have the "Units Sold" field, and my life is complicated by it. Any help would be greatly appreciated.

Thanks,
Ronald

Just as a side note, you can use Excel's MEDIAN Function to return a Median value for a Range of Prices.

Sorry for the delay in getting back to you. Took me a while to work out the logic on this. I used ADezii's code as a base and this code caters for the Units Sold factor. I've tested it and it worked for me.

Sorry forgot to add the query which is slightly different from ADezii's

Just as a side note, you can use Excel's MEDIAN Function to return a Median value for a Range of Prices.

Hi ADezii, thanks for your help. I've tried following your steps in the code and I got an error reading: "Undefined Function 'fCalculateMedian' in expression" Seems like i'm having trouble adding a user defined function.

I've added it using the method you described:
1) Go to Modules
2) Double click anyone of them
3) Insert Module
4) Copy and paste your Public Function
5) Named the module fCalculateMedian

But the query says it doesnt exist. Anyhelp?

Also I was looking at your code, if I understand the logic, it seems you think the records in the table each correpond to 1 unit sold, and going to the middle of the number of records would work. But actually there are different number of units sold for each price, so it might be that the median value lies in record 30 out of 100 if there are many "Units Sold" in the first few records. This explains why the situation is different from what Excel's median function does, which takes each price as a single occurance. Of cos, perhaps I understood your code wrongly...

But anyhow, if you can help me with getting the new function to work (tells you how new I am to Access programming huh?) I can then give your code a run and tweak it from there. (strangely, the function appears in the expression builder under Functions->MyDatabaseName....)

Thanks for your help thus far, looking forward to your response.

Ronald

Hi Ronald

Insert New Module if you haven't already got one or just add my function (which expands ADezii's to cater for the Units sold) by copying and pasting it into any existing module (not a form).

The module does not have to be named fCalculateMedian as this is the name of the function and it doesn't matter which module it is in. In fact naming the module fCalculateMedian is probably confusing Access.

Let me know if you still have problems.

Mary

Hi Ronald

Insert New Module if you haven't already got one or just add my function (which expands ADezii's to cater for the Units sold) by copying and pasting it into any existing module (not a form).

The module does not have to be named fCalculateMedian as this is the name of the function and it doesn't matter which module it is in. In fact naming the module fCalculateMedian is probably confusing Access.

Let me know if you still have problems.

Mary

AMAZING! Thanks Mary! Your code works perfectly. Reading it I see the logic in how you went about it. Thank you so much!!! (This is a really great forum, tons of cool stuff and ppl here)

AMAZING! Thanks Mary! Your code works perfectly. Reading it I see the logic in how you went about it. Thank you so much!!! (This is a really great forum, tons of cool stuff and ppl here)

You're welcome Ronald.

Glad you're enjoying the forum.

Mary

Hi, I came across this post and I am trying to do pretty much the same function. In mine, I am trying to find the median of certain values, based on the "Department" grouping. I used ADezii's code and query; I adjusted the code to what I thought should be correct. Unfortunately I encounter an error saying "Data type mistmatch in the criteria"...

This error comes up on this portion of the code " Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)"

Here is the function and the query to what I adjusted them to. If anyone has any ideas because help would be appreciated.

Query:
SELECT DISTINCT Department, fCalculateMedian([Department],[Total_Amount]) AS Median
FROM Gifts
ORDER BY Department;


VBA Code:

Hi, I came across this post and I am trying to do pretty much the same function. In mine, I am trying to find the median of certain values, based on the "Department" grouping. I used ADezii's code and query; I adjusted the code to what I thought should be correct. Unfortunately I encounter an error saying "Data type mistmatch in the criteria"...

This error comes up on this portion of the code " Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)"

Here is the function and the query to what I adjusted them to. If anyone has any ideas because help would be appreciated.

Query:
SELECT DISTINCT Department, fCalculateMedian([Department],[Total_Amount]) AS Median
FROM Gifts
ORDER BY Department;


VBA Code:

Expand|Select|Wrap|Line Numbers

1. Public Function fCalculateMedian(strArea As String, curPrice As Currency)
2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
3. Dim intNumOfRecords As Integer, curPriceValue As Currency
4.  
5.      MySQL = "SELECT Gifts.Department, Gifts.Total_amount FROM Gifts "
6.     MySQL = MySQL & "WHERE Gifts.Total_Amount ='" & strArea & "' ORDER BY Gifts.Department, Gifts.Total_Amount;"
7.  
8.     Set MyDB = CurrentDb()
9.     Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
10.  
11.     MyRS.MoveLast: MyRS.MoveFirst
12.  
13.     intNumOfRecords = MyRS.RecordCount
14.     If intNumOfRecords = 0 Then Exit Function
15.  
16.     If intNumOfRecords Mod 2 = 0 Then     'Even number of Records
17.       MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
18.         curPriceValue = MyRS![Price]      '1st value to average
19.       MyRS.MoveNext
20.         curPriceValue = curPriceValue + MyRS![Price]               '2nd value to average added to 1st value
21.         fCalculateMedian = Format$(curPriceValue / 2, "Currency")  'Average them out
22.     Else   'Odd number of Records
23.       MyRS.Move (intNumOfRecords \ 2)
24.       fCalculateMedian = Format$(MyRS![Price], "Currency")
25.     End If
26.  
27.     MyRS.Close
28.  
29. End Function

The mistake is in Line #6, but the actual Error occurs when you try to Open the Recordset based on MySQL. It appears as though, in your WHERE Clause, you are attempting to equate a Currency Value (Gifts.Total_Amount) with a String Value (strArea). Therein lies your mismatch.

I followed your advice and adjusted the MYSQL, as well as a few other problems I found and now it gets past the part where I was originally having the problem. Now it is getting stuck on the code on line 19:

curPriceValue = MyRS![Price]

Because I am new to using VBA with Access, I am unsure what the ![Price] does. So it is hard for me to figure out a solution. The error I am receiving says:

Run-tim error '3265':
Item not found in this collection.


I don't know if the problem could be the version of Access that I am using, I am using Access 2003. I thought maybe the function of ![Price] is not available with my version of Access.




Incase it is needed, here is an updated version of my code:


Thanks for your help,
-Jedd

...curPriceValue = MyRS![Price]
...
Run-tim error '3265':
Item not found in this collection.

Hi. This error is nothing to do with the version of Access; it simply means that you do not have a field called Price in the recordset you are looping through.

The general syntax of assigning values to variables from field references in a recordset is like this:
Remember that you have to adjust the code that was originally written for another user with a different problem for your particular circumstances, and if you don't have a field named Price you either need to remove the field reference from the loop or substitute one that is named correctly for your data.

You also need to understand what it is that the function is doing before you can adapt it, and at present I don't think you do...

**Edit: The whole purpose of the function as provided is to calculate the median of the Price. If you are computing the median of a different value you must substitute all occurrences of Price in the code with the actual field name in your own application.

-Stewart

This took me so long to post on here because I haven't been able to try it. I was out from work for a few days with pneumonia... Anyways I now understand that ![price] just extracts the price column information from the recordset, I was just unfamiliar with it.

I have run into quite a few problems and every time I go to post on here what it is, I figure out the problem. Finallry the results are correct. I really appreciate all of your help. I would not have been able to do this without all of you.


-Jedd

This took me so long to post on here because I haven't been able to try it. I was out from work for a few days with pneumonia... Anyways I now understand that ![price] just extracts the price column information from the recordset, I was just unfamiliar with it.

I finally ran the query and it looked as though it was working. Upon analyzing the results are not correct. It is not delivering the median. What it is doing is if there are two values that are the same it only counts them once. So in the example below it only appears to think there are 5 records and will give a median of 36000 because that is the median of all the distinct prices. I will try to figure out how to fix this, but I think that may be over my skill level, so help would be appreciated.

Here is one example of the problem, This is where the data is coming from:

GEO Total_Amount
BLT 58,908.47
BLT 49,950.56
BLT 36,000.00
BLT 25,000.00
BLT 25,000.00
BLT 25,000.00
BLT 25,000.00
BLT 25,000.00
BLT 25,000.00
BLT 24,934.64

The result is:
Median GEO
$36,000.00 BLT


Here is my code:

Expand|Select|Wrap|Line Numbers

1.  Public Function fCalculateMedian2(strArea As String, curPrice As Currency)
2. Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String
3. Dim intNumOfRecords As Integer, curPriceValue As Currency
4.  
5. MySQL = "SELECT test.geo, test.Total_Amount FROM test "
6. MySQL = MySQL & "WHERE test.geo= '" & strArea & "' group BY test.geo, test.Total_Amount;"
7.  
8.  Set MyDB = CurrentDb()
9. Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)
10.  
11. MyRS.MoveLast: MyRS.MoveFirst
12.  
13. intNumOfRecords = MyRS.RecordCount
14. If intNumOfRecords = 0 Then Exit Function
15.  
16. If intNumOfRecords Mod 2 = 0 Then     'Even number of Records
17.   MyRS.Move (intNumOfRecords \ 2) - 1   'Move half-way point
18.     curPriceValue = MyRS![Total_Amount]      '1st value to average
19.   MyRS.MoveNext
20.     curPriceValue = curPriceValue + MyRS![Total_Amount]               '2nd value to average added to 1st value
21.     fCalculateMedian2 = Format$(curPriceValue / 2, "Currency")  'Average them out
22. Else   'Odd number of Records
23.   MyRS.Move (intNumOfRecords \ 2)
24.   fCalculateMedian2 = Format$(MyRS![Total_Amount], "Currency")
25. End If
26.  
27. MyRS.Close
28. End Function
29.  
30.  

-Jedd

The results are correct since you applied a GROUP BY Clause whai would have the same effect as passing the following data to the Function:
As you can see, jedgretzky, the Median Value is exactly what it should be, namely $36,000/BLT