median

[Gunit]

Could someone please help me? There is no function in Access 2000 to compute the median of a column of numbers (Currency) from a table.

 

[KenHigg]

You should be able to build a custom function to do this...

Have you tried this?

kh

 

[Gunit]

Yes but I'm only a beginner at Access. I used some VBA code off of Microsofts support sites but couldn't get it to work in a standard query.

 

[KenHigg]

Hum...

Will have your dataset in a table or query, ready to use or are gong to crunch with arrays, etc or something off the wall?

kh

 

[EMP]

Take a look at this thread
http://www.access-programmers.co.uk/forums/showthread.php?t=73588

Since your data is in a row, Jon K's query example in the thread using the Excel WorksheetFunction.Median should work.

_

 

[Gunit]

Thanks for the link. I need to calculate the median based on 4 different criteria specs.

Age range
Gender
Race
Degree

I want to find the median of wages for any combination of the four above. The column named [wages] looks like this in query design view: Expr1: Avg([WgTtl]/100*4). So I'm actually calling, then calculating. From the Expr1, I need to take the results and find the median. I'm also doing some totaling depending on what I'm querying.

 

[KenHigg]

The average will not work for a location of the data?

kh

 

[Gunit]

I'm already calculating the average. But average and median return different things. The median calculates the 50th percentile. So basically, I need to know the middle wage. Access has built in functions. By totaling, it will

avg
sum
min
max
......etc.

By turning on the excel obj library, can you call the median function by totaling in standard query view?

 

[KenHigg]

I think that's the solution that the reply from EMP was alluding to...

kh

 

[Gunit]

Yes. For calculating vertical data in a form however I want to know if you can call it from query design rather than from a form. I have criteria in query design that I want to be true and from those results, I want to find the median of a data column.

 

[KenHigg]

If you're still hung up with this I'll see if I can do a function for it...

kh

 

[Gunit]

That would be great Ken. I am leaving for the day though. I will post details tomorrow morning.

 

[KenHigg]

Gunit - Here's an example of how to do the median function. For this example:

1. You have the data in a table
2. You have to presort the data using the query

There are ways to refine this. I'll assume you can figure out what's happening so I'll spare you a wordy explaination...

kh

 

[Gunit]

Ken - I've posted a sample of my project to get a better idea of what I'm doing.

 

[KenHigg]

So you don't think you can adapt the example I posted?

kh

 

[Gunit]

Please see attached. I need for access to calculate median as excel does. I need the median of the values within a range...not the middle value within a range. I would prefer to have a function that would call the values from a column and display. I'm sorry I've been so difficult to understand.

 

[KenHigg]

Did you press the button on the form and look into how it works? It looks like it works the same to me? The median value returned is the same anyway...

kh

 

[Gunit]

Sorry Ken. I wasn't paying attention. Okay. This will work but I'm getting an overflow error off of this statement....f_getMedian = (intNumOne + intNumTwo) / 2...when trying to use function. Any ideas?

 

[KenHigg]

How large are the number you're using? You may need to replace the dim x as integers to dim x as long so the variable will hold larger numbers.

kh

 

[Gunit]

Thanks Ken - That took care of the overflow error and the form works wonderfully. However when I try to use the function through a query, it returns a "0" value. My record count = 4102 and the highest numeric string = 6 integers. Any ideas?