P: 7

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
 
Share this Question
Expert Mod 10K+
P: 14,534

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

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
  Expert 5K+
P: 8,623

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: Your Table Name is tblValues.
 tblValues contains the Fields [Area] (TEXT), [Units Sold] (LONG), [Price] (CURRENCY).
Procedure: 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.
 SELECT DISTINCT tblValues.Area, fCalculateMedian([Area],[Price]) AS Median

FROM tblValues

ORDER BY tblValues.Area;
 Copy the Function Code below to a Standard Code Module.
 Public Function fCalculateMedian(strArea As String, curPrice As Currency)

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String

Dim intNumOfRecords As Integer, curPriceValue As Currency


MySQL = "SELECT tblValues.Area, tblValues.Price FROM tblValues "

MySQL = MySQL & "WHERE tblValues.Area='" & strArea & "' ORDER BY tblValues.Area, tblValues.Price;"


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 halfway point

curPriceValue = MyRS![Price] '1st value to average

MyRS.MoveNext

curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value

fCalculateMedian = Format$(curPriceValue / 2, "Currency") 'Average them out

Else 'Odd number of Records

MyRS.Move (intNumOfRecords \ 2)

fCalculateMedian = Format$(MyRS![Price], "Currency")

End If


MyRS.Close

End Function
 Sample Output is listed below, if you have any problems, let us know.
OUTPUT: 
Area Median

California $110,000.00

New York $115,000.00

Philadelphia $116,000.00
  Expert 5K+
P: 8,623

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.
  Expert Mod 10K+
P: 14,534

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. 

Public Function fCalculateMedian(strArea As String) As Currency

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String

Dim intNumUnitsSold As Integer

Dim intRecordNum As Integer

Dim curPriceValue As Currency


MySQL = "SELECT tblValues.[Units Sold], tblValues.Price FROM tblValues "

MySQL = MySQL & "WHERE tblValues.Area='" & strArea & "' ORDER BY tblValues.Price;"


Set MyDB = CurrentDb()

Set MyRS = MyDB.OpenRecordset(MySQL, dbOpenSnapshot)


MyRS.MoveFirst


Do Until MyRS.EOF

intNumUnitsSold = intNumUnitsSold + MyRS![Units Sold]

MyRS.MoveNext

Loop


If intNumUnitsSold = 0 Then Exit Function


MyRS.MoveFirst

If intNumUnitsSold Mod 2 = 0 Then 'Even number of Records

Do Until MyRS.EOF

intRecordNum = intRecordNum + MyRS![Units Sold]

If intRecordNum = (intNumUnitsSold / 2) Then

curPriceValue = MyRS![Price] '1st value to average

MyRS.MoveNext

curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value

fCalculateMedian = Format$(curPriceValue / 2, "Currency") 'Average them out

MyRS.MoveLast

ElseIf intRecordNum > (intNumUnitsSold / 2) Then

fCalculateMedian = Format$(MyRS![Price], "Currency") 'Average price

MyRS.MoveLast

End If

MyRS.MoveNext

Loop

Else 'Odd number of Records

Do Until MyRS.EOF

intRecordNum = intRecordNum + MyRS![Units Sold]

If intRecordNum > (intNumUnitsSold / 2) Then

fCalculateMedian = Format$(MyRS![Price], "Currency") 'Average price

MyRS.MoveLast

End If

MyRS.MoveLast

Loop

End If


MyRS.Close

Set MyRS = Nothing

Set MyDB = Nothing

End Function

  Expert Mod 10K+
P: 14,534

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

SELECT DISTINCT [tblValues].[Area], fCalculateMedian([Area]) AS Median

FROM tblValues

ORDER BY [tblValues].[Area];

 
P: 7

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
  Expert Mod 10K+
P: 14,534

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

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)
  Expert Mod 10K+
P: 14,534

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
 
P: 8

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:  Public Function fCalculateMedian(strArea As String, curPrice As Currency)

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String

Dim intNumOfRecords As Integer, curPriceValue As Currency


MySQL = "SELECT Gifts.Department, Gifts.Total_amount FROM Gifts "

MySQL = MySQL & "WHERE Gifts.Total_Amount ='" & strArea & "' ORDER BY Gifts.Department, Gifts.Total_Amount;"


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 halfway point

curPriceValue = MyRS![Price] '1st value to average

MyRS.MoveNext

curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value

fCalculateMedian = Format$(curPriceValue / 2, "Currency") 'Average them out

Else 'Odd number of Records

MyRS.Move (intNumOfRecords \ 2)

fCalculateMedian = Format$(MyRS![Price], "Currency")

End If


MyRS.Close


End Function
  Expert 5K+
P: 8,623

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:  Public Function fCalculateMedian(strArea As String, curPrice As Currency)

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String

Dim intNumOfRecords As Integer, curPriceValue As Currency


MySQL = "SELECT Gifts.Department, Gifts.Total_amount FROM Gifts "

MySQL = MySQL & "WHERE Gifts.Total_Amount ='" & strArea & "' ORDER BY Gifts.Department, Gifts.Total_Amount;"


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 halfway point

curPriceValue = MyRS![Price] '1st value to average

MyRS.MoveNext

curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value

fCalculateMedian = Format$(curPriceValue / 2, "Currency") 'Average them out

Else 'Odd number of Records

MyRS.Move (intNumOfRecords \ 2)

fCalculateMedian = Format$(MyRS![Price], "Currency")

End If


MyRS.Close


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.  MySQL = MySQL & "WHERE Gifts.Total_Amount ='" & strArea & "' ORDER BY
 
P: 8

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:
Runtim 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:  Public Function fCalculateMedian(strArea As Long, curPrice As Currency)

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String

Dim intNumOfRecords As Integer, curPriceValue As Currency


MySQL = "SELECT Gifts.Department, Gifts.Total_amount FROM Gifts "

MySQL = MySQL & "WHERE Gifts.Department =" & strArea & " ORDER BY Gifts.Department, Gifts.Total_Amount;"


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 halfway point


curPriceValue = MyRS![Price] '1st value to average


MyRS.MoveNext

curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value

fCalculateMedian = Format$(curPriceValue / 2, "Currency") 'Average them out

Else 'Odd number of Records

MyRS.Move (intNumOfRecords \ 2)

fCalculateMedian = Format$(MyRS![Price], "Currency")

End If


MyRS.Close


End Function
Thanks for your help,
Jedd
  Expert Mod 2.5K+
P: 2,545

...curPriceValue = MyRS![Price]
...
Runtim 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:  somevar = RecordsetVar![some field name]
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
 
P: 8

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
  Expert 5K+
P: 8,623

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:  Public Function fCalculateMedian2(strArea As String, curPrice As Currency)

Dim MyDB As DAO.Database, MyRS As DAO.Recordset, MySQL As String

Dim intNumOfRecords As Integer, curPriceValue As Currency


MySQL = "SELECT test.geo, test.Total_Amount FROM test "

MySQL = MySQL & "WHERE test.geo= '" & strArea & "' group BY test.geo, test.Total_Amount;"


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 halfway point

curPriceValue = MyRS![Total_Amount] '1st value to average

MyRS.MoveNext

curPriceValue = curPriceValue + MyRS![Total_Amount] '2nd value to average added to 1st value

fCalculateMedian2 = Format$(curPriceValue / 2, "Currency") 'Average them out

Else 'Odd number of Records

MyRS.Move (intNumOfRecords \ 2)

fCalculateMedian2 = Format$(MyRS![Total_Amount], "Currency")

End If


MyRS.Close

End Function


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: 
GEO Total_Amount

BLT 24,934.64

BLT 25,000.00

BLT 36,000.00

BLT 49,950.56

BLT 58,908.47
As you can see, jedgretzky, the Median Value is exactly what it should be, namely $36,000/BLT     Question stats  viewed: 1928
 replies: 16
 date asked: May 31 '07
