Median Value Question (Different from previous ones)

[ronaldchua]

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,00 0 / 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

[MMcCarthy]

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,00 0 / 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

[ronaldchua]

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

[ADezii]

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,00 0 / 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.
   [CODE=vb]SELECT DISTINCT tblValues.Area, fCalculateMedia n([Area],[Price]) AS Median
   FROM tblValues
   ORDER BY tblValues.Area;[/CODE]
2. Copy the Function Code below to a Standard Code Module.
   [CODE=vb]Public Function fCalculateMedia n(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.OpenRecord set(MySQL, dbOpenSnapshot)
   
   MyRS.MoveLast: MyRS.MoveFirst
   
   intNumOfRecords = MyRS.RecordCoun t
   If intNumOfRecords = 0 Then Exit Function
   
   If intNumOfRecords Mod 2 = 0 Then 'Even number of Records
   MyRS.Move (intNumOfRecord s \ 2) - 1 'Move half-way point
   curPriceValue = MyRS![Price] '1st value to average
   MyRS.MoveNext
   curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value
   fCalculateMedia n = Format$(curPric eValue / 2, "Currency") 'Average them out
   Else 'Odd number of Records
   MyRS.Move (intNumOfRecord s \ 2)
   fCalculateMedia n = Format$(MyRS![Price], "Currency")
   End If
   
   MyRS.Close
   End Function[/CODE]
3. Sample Output is listed below, if you have any problems, let us know.

OUTPUT:
[CODE=text]
Area Median
California $110,000.00
New York $115,000.00
Philadelphia $116,000.00[/CODE]

[ADezii]

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,00 0 / 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.

[MMcCarthy]

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.
[CODE=vb]
Public Function fCalculateMedia n(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.OpenRecord set(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 = (intNumUnitsSol d / 2) Then
curPriceValue = MyRS![Price] '1st value to average
MyRS.MoveNext
curPriceValue = curPriceValue + MyRS![Price] '2nd value to average added to 1st value
fCalculateMedia n = Format$(curPric eValue / 2, "Currency") 'Average them out
MyRS.MoveLast
ElseIf intRecordNum > (intNumUnitsSol d / 2) Then
fCalculateMedia n = 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 > (intNumUnitsSol d / 2) Then
fCalculateMedia n = 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
[/CODE]

[MMcCarthy]

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

[CODE=sql]
SELECT DISTINCT [tblValues].[Area], fCalculateMedia n([Area]) AS Median
FROM tblValues
ORDER BY [tblValues].[Area];
[/CODE]

[ronaldchua]

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 'fCalculateMedi an' 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 fCalculateMedia n

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

[MMcCarthy]

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 fCalculateMedia n as this is the name of the function and it doesn't matter which module it is in. In fact naming the module fCalculateMedia n is probably confusing Access.

Let me know if you still have problems.

Mary

[ronaldchua]

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 fCalculateMedia n as this is the name of the function and it doesn't matter which module it is in. In fact naming the module fCalculateMedia n 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)

[MMcCarthy]

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

[jedgretzky]

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.OpenRecord set(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, fCalculateMedia n([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 half-way 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

[ADezii]

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.OpenRecord set(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, fCalculateMedia n([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 half-way 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_Am ount) with a String Value (strArea). Therein lies your mismatch.
[CODE=sql]MySQL = MySQL & "WHERE Gifts.Total_Amo unt ='" & strArea & "' ORDER BY [/CODE]

[jedgretzky]

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:

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 half-way 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

[Stewart Ross]

...curPriceValu e = 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:

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