UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Finding A Median Value, Access 2000    
 
   
TinyGiant2010
post Dec 23 2017, 09:53 AM
Post#1



Posts: 191
Joined: 1-June 10



I have a sales report that shows Count, Max, Min, Average values for sale prices; however, after sorting Ascending on price, I can't seem to determine a MEDIAN price.

=Median([sale p$]) does not work
=Count([sale p$]) works
=Max([sale p$]) works
=Min([sale p$]) works
=Avg([sale p$]) works

The MEDIAN value is the one that shows 50% above and 50% below this value; and I'd like to know what it takes to get it displayed on a report.

I can run a query, sort the price values, note number of records, then divide that by 2, then go to the record and see the value I want; then record it "by hand" but I'd really like to automate this.

Please help.
Go to the top of the page
 
GroverParkGeorge
post Dec 23 2017, 10:15 AM
Post#2


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


What does it mean to say that "...it doesn't work"?

Do you get the wrong value, no value, an error?

Do you get different results using different sort orders?

Are your values all currency amounts?
Go to the top of the page
 
GroverParkGeorge
post Dec 23 2017, 10:17 AM
Post#3


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


I was also looking at the standard aggregate functions. How do you calculate Median()?
Go to the top of the page
 
projecttoday
post Dec 23 2017, 10:26 AM
Post#4


UtterAccess VIP
Posts: 10,160
Joined: 10-February 04
From: South Charleston, WV


That's simply because there is no Median function built into Access VBA. You have to write one yourself. And then you can use it the same as the others.
Go to the top of the page
 
GroverParkGeorge
post Dec 23 2017, 10:30 AM
Post#5


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


Yup, that's my point. It must be a UDF.
Go to the top of the page
 
TinyGiant2010
post Dec 23 2017, 10:36 AM
Post#6



Posts: 191
Joined: 1-June 10



=Median([sale p$]) does not work
Let me explain.

Suppose I have 103 values, in ascending low-high order, to evaluate.

There will be 51 values lower than the one I seek, then target value I seek, then 51 values higher than what I seek. This is the MEDIAN.

Next, Suppose there are 104 values, there will be 51 below; 2 in the target range, and 51 above. I need to code the method to tell MEDIAN to return the higher value in the target range.

I did not know Access did not have this built in, and I sure don't know how to code it to include in the report footer.

Thanks for the eyeballs!

(btw - I have no clue what a UDF is)
This post has been edited by TinyGiant2010: Dec 23 2017, 10:40 AM
Go to the top of the page
 
projecttoday
post Dec 23 2017, 11:00 AM
Post#7


UtterAccess VIP
Posts: 10,160
Joined: 10-February 04
From: South Charleston, WV


I did a little googling and couldn't find an already-coded Median function. (Maybe a lot of googling would turn one up.) I found a lot of discussion about DMedian, but that's not what you want, really. The thing with median is that you have to sort the data. Not so with Avg. So it seems to me you have to copy the report's recordset to another recordset, sort that, calculate the middle member's position, and return that value.

Good luck.
Go to the top of the page
 
GroverParkGeorge
post Dec 23 2017, 11:05 AM
Post#8


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


UDF --> User Defined Function, which is what Robert found for you.

I guess described would be more accurate than found.
This post has been edited by GroverParkGeorge: Dec 23 2017, 11:50 AM
Go to the top of the page
 
orange999
post Dec 23 2017, 11:51 AM
Post#9



Posts: 1,861
Joined: 10-February 08
From: Ottawa, Ont, Canada; West Palm Beach, FL


Here is a UDF for Median that I gleaned a few years back.

When there is an odd number of values, it selects the middle (median) value.
When there is an even number of values, it finds the middle pair and averages them to get median.

CODE
'---------------------------------------------------------------------------------------
' Procedure : MedianF
' Author    : Jack/found as below
' Date      : 12-09-2012
' Purpose   : from SROss81
' http://www.access-programmers.co.UK/forums/showthread.php?p=1190336#post1190336
'
'Median:
'denoting or relating to a value or quantity lying at the midpoint of a frequency
'distribution of observed values or quantities, such that there is an equal
'probability of falling above or below it.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'--------------------------------------------------------------------------
'
Function MedianF(pQuery As String, pfield As String) As Long

    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim n As Long
    Dim sglHold As Long
10  On Error GoTo MedianF_Error

20  strSQL = "SELECT " & pfield & " from " & pQuery & " WHERE " & pfield & ">0 Order by " & pfield & ";"
30  Set rs = CurrentDb.OpenRecordset(strSQL)
40  rs.MoveLast
50  n = rs.RecordCount
60  rs.Move -Int(n / 2)

70  If n Mod 2 = 1 Then    'odd number of elements
80      MedianF = rs(pfield)
90  Else    'even number of elements
100     sglHold = rs(pfield)
110     rs.MoveNext
120     sglHold = sglHold + rs(pfield)
130     MedianF = sglHold / 2
140 End If
150 rs.Close

MedianF_Exit:
160 Exit Function

MedianF_Error:
170 MsgBox "Error " & err.number & " in line " & Erl & " (" & err.Description & ") in procedure MedianF of Module AWF_Related"
180 Resume MedianF_Exit
End Function
Go to the top of the page
 
projecttoday
post Dec 23 2017, 12:42 PM
Post#10


UtterAccess VIP
Posts: 10,160
Joined: 10-February 04
From: South Charleston, WV


Okay, orange and TinyGiant, this will find a median in the specified table. To be assured of the correct median for the report, lest another entry be made between time, yuo need to adapt this so it gets its data from the report. As I mentioned I found several these but didn't post them for that reason.
Go to the top of the page
 
Jeff B.
post Dec 23 2017, 01:25 PM
Post#11


UtterAccess VIP
Posts: 10,140
Joined: 30-April 10
From: Pacific NorthWet


Do you have the option of calling Excel's Median() function?
Go to the top of the page
 
TinyGiant2010
post Dec 25 2017, 08:58 AM
Post#12



Posts: 191
Joined: 1-June 10



Merry Christmas everyone!

Thanks for the code Orange. I've not incorporated it yet.

I don't know how to use Excel as a feature to "number crunch" within Access; and is beyond my competence level.

I have a query that sorts Ascending the sales values I am evaluating. This query will not be updated while the report processes; I am a one-person operation here.
It is easy enough for me to just look at the query itself, count the records, then determine the midpoint "area" and determine the median.

I think the easy answer for me is to open the query and evaluate the median and keep the query open, then run the report and let it ask me to input the median value manually.

The issue I was trying to avoid was having to do this "work" repeatedly, should my areas of analysis change, ie I evaluate my county, and my county has 14 townships. I also evaluate one specific township for median values. It would be nice, with just the touch of a "run report" button to be able to select a township of interest out of the other 13 and see what's what, and have that report show me instantly; rather than approaching this from an "open the query and determine, then run the report and manually enter" approach.

I'd really hoped that Access had an elegant way to do this, or a built-in function; something I had missed or overlooked.

Thanks everyone for your attention and help on this.

Have a happy and safe Christmas season!
Go to the top of the page
 
GroverParkGeorge
post Dec 25 2017, 10:14 AM
Post#13


UA Admin
Posts: 33,760
Joined: 20-June 02
From: Newcastle, WA


It seems to me that a properly crafted VBA function is the most "elegant" way to do this with Access. The next best choice would be to learn how to call the Excel function to do it.

However, it does require moving beyond entry level skills, for sure. Another alternative might actually be to do this part directly in Excel, in a kind of round-about way.

Here's how I might try that approach, as the third option.

In an Excel worksheet, create a table linked to the appropriate query in your mdb. (And, as an aside, you are using a long-out-of-support version of Office, so this approach might not even work. I don't recall what Excel 2000 did or didn't support. It may not be able to do this.) Now, apply the Excel Median() function to the records in that linked Excel sheet. You can then link that Excel sheet, with the cell containing the Median() result, back into Access and incorporate the value that way.

Again, that's a wild work-around, and may not be possible. I'd probably go with the first option, a VBA function anyway.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th October 2018 - 06:18 AM