Hi,
You can, technically, but through a VBA function: a VBA function would
call your Excel-VBA method, and then assuming you have the "Reference..."
rightly set, and the arguments of your Excel method properly "typed", ...
Maybe it is preferable to rank "by category", and find the 50 centile.
Assuming a table like
River quarter Test Measure ' fields
Athabasca winter2001 Al 0.0001 ' data sample
then a first query like:
=============
SELECT a.river, a.quarter, a.test, a.measure, COUNT(*) as rank
FROM myData As a INNER JOIN myData As b
ON a.river = b.river
AND a.test= b.test
AND a.quarter = b.quarter
AND a.measure >= b.measure
GROUP BY a.river, a.quarter, a.test, a.measure
HAVING COUNT(*) >= 0.5* (SELECT COUNT(*)
FROM myTable As c
WHERE a.river=c.river
AND a.test= c.test
AND a.quarter=b.quarter)
===============
would return the upper half most test measure. In fact, it counts the number
of test-value, measure, lower or equal to itself, for the same river, same
quarter, same test. Save that query, say, q1, then
SELECT river, quarter, test, MIN(measure) as Centile50
FROM q1
GROUP BY river, quarter, test
would supply the minimum value of that upper half, or the 50 centile, per
river, per quarter, per test.
If you don't have a quarter field, but a dateTimeStamp, instead of
z.quarter, use Format( z.DateTimeStamp, "q-yyyy"), as example (but can be
quite slow, since not indexed).
Hoping it may help,
Vanderghast, Access MVP
Dirk said:
Hello,
Thanks again for your reply. I don't have any NULL
values, but I did have a space in one of the field names
used. I will try this again.
I also wonder whether I should be using a second query
based on the initial one, instead of relying on one
single query to compute the Medians. What do you think?
I am using the View, Totals command in the query to group
data: first by river segment, then by season, then by
parameter, then by form, and then comes the actual
numerical result. For example, for Athabasca River would
have four seasons of data (fall, winter, etc.). Then for
a parameter like Aluminum Dissolved, I would have several
results - for which I need to calc the median (because it
is less sensitive to outliers - a common practice in
water quality analysis).
Is it also possible to call up Excel's MEDIAN function
from within Access 2002?
Sincerely,
Dirk
North/South Consultants Inc.
Winnipeg, Manitoba
Canada
www.nscons.ca
-----Original Message-----
Hi,
It also assumes that there is no space in the table name, and that the
table name is legal, without having to use [ ].
Vanderghast, Access MVP
Dear Mr. Vanderghast.
Thank you for pointing me to the code. I created a Module
in Access and pasted the code. I then entered the
function in the Access query. Where it says "x" for
percentile, I entered 0.5. However, I am seeing aggregate
errors or a message box asking me to enter a Table name -
even though I specified the table name in the function in
the query.
This sounds crazy, but how exactly should I use the
function?
Sincerely,
Dirk
-----Original Message-----
Hi,
If the 50 percentile is OK for you, as approximation,
then
http://www.mvps.org/access/queries/qry0019.htm can be
interesting (note that
the article assumes there is no NULL value in the field).
Hoping it may help,
Vanderghast, Access MVP
I want to calculate the Median in a Microsoft Access
query. How do I do this?
Our office is using Excel to process large amounts of
water quality data. PivotTables have helped summarize
some of the data. Unfortunately, there is no Median
function available in Excel's PivotTables (why?).
We brought the data into an Access 2002 table, and then
created a simple query grouping the water data by river
segment and then season. Unfortunately, there is no
function to allow us to calculate the median in Access
queries.
Sincerely,
Dirk
North/South Consultants Inc.
Winnipeg, Manitoba
Canada
(Please post your reply to this newsgroup - thanks
)
.
.
