Median

[Guest]

I want to get a median for a set of data in a report. I tried to make two
queries with top 50% of the data in both acending and descending order; then
take the average of the max value from the acending order query and the min
value from the descending order query. The logic seems to be right but I
couldn't get the desired result.

I am using Access 2000. Any idea on how to find to find the median for a
report? (much like the same way an average is found). And does my current
logic makes sense? Thanks in advance

 

[Al Camp]

Check out Google Access groups, and search under "calculate" "median"
There are appear to be quite a few hits on it.
Also, try Mike Walsh's...
http://www.mvps.org/access/queries/qry0019.htm
it appears to be cited quite often.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"questionnaire database analyst"

 

[Guest]

Thanks for your reply. However, I cannot get that to work.

This is what I have typed in my report:
=Xpercentile("sco","Rexitcompany2","0.5")

sco is the name of the field
Rexitcompany2 is the name of that query
It is for a continous form and i have null value in the table too. Any idea

 

[Al Camp]

What does a null represent?
If it constitutes a zero, and you want it included, then you'll probably
have to adjust the calculations in the function code (using the NZ function)
to get a correct calculation.
If nulls aren't included in the median calculation, then you'll probably
have to filter out those values. Perhaps a pass thru query..., or a basic
Is Not Null criteria.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"questionnaire database analyst"

 

[Guest]

I have already filtered out the null value by adding "Is Not Null" into the
criteria of the query. However, the following error still comes up:
"Expected parameter:3"

 

[Al Camp]

Sounds like you might have introduced a "syntax" or "naming" problem into
the problem, but can't be sure.

Also, the DMin and DCount in your XPercentile function is based on the
original table data, so the nulls may be being re-introduced to the
calculations.

Try some problem determination... since you want to dump nulls anyway, do a
"MakeTable" query to create a table/recordset from your data that has no
nulls. Run your XPercentile function and your query against that. If you
still have problems, we can put the Null issue aside, and you'll know your
function syntax or naming may be at fault.

I have not personally used this median function... but I have no doubt that
it works when it is presented with NoNull data and proper arguments.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

"questionnaire database analyst"