Median in a query

[nick.vitone]

Hi,

I'm somewhat of a novice at Access, and I have no experience
programming whatsoever. I'm attempting to calculate the statistical
median in a query. I need to "Group by" one column and find the median
of the another, though I'm not sure how. I've been able to add the
"Median" function (from the Microsoft Access Help Archive), but I can't
figure out how to incorporate that into the Totals. Do I need to use
the "expression" and then build the expression?

Thanks for any help,

Victor

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Do you mean you are using the function provided in MS KB article
Q210581? If so you can include the call to the function in a query like
this:

SELECT Median(table_name, field_name), <other columns>
FROM table_name
WHERE <criteria>
GROUP BY Median(table_name, field_name) , <other columns>

Substitute your table & field name for "table_name" and "field_name,"
respectively.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkHCjYechKqOuFEgEQLP0ACffvtQipID8zVlwFkn1btHfr/+7n8An0OZ
c4p03mDiHN7jo28XIOs3Y1VH
=seWN
-----END PGP SIGNATURE-----
ni*********@gmail.com wrote:

Hi,

I'm somewhat of a novice at Access, and I have no experience
programming whatsoever. I'm attempting to calculate the statistical
median in a query. I need to "Group by" one column and find the median
of the another, though I'm not sure how. I've been able to add the
"Median" function (from the Microsoft Access Help Archive), but I can't
figure out how to incorporate that into the Totals. Do I need to use
the "expression" and then build the expression?

[Brian Mullin via AccessMonster.com]

You're right, if you're looking in query design view, there is no median
function. I think there's everything else, sum, mean count etc.

I ran into this problem with my last job. We wound up having to run
Medians using an Access Ad In called "Total Access Statistics." This type
of thing is also really easy to do in something like SPSS.

You can do it in Excel, but it's drudgery arranging all the cells before
you can do the Medians. It may be possible to do with an SQL statement,
but I'm kind of new to writing SQL.

--
Message posted via http://www.accessmonster.com

[nick.vitone]

Yes, that's the one.

Maybe I input the function wrong. When I try that I get an error
saying the function Median is not defined.

[MGFoster]

ni*********@gmail.com wrote:

Yes, that's the one.

Maybe I input the function wrong. When I try that I get an error
saying the function Median is not defined.

Make sure you've put the function in a regular module, not a form's
module. Make sure the function is a public function:

Public Function Median(....) As ....

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[nick.vitone]

I hate to treat you as a personal tutor, but I still can't seem to get
this to work.

SELECT Sample.Groups, Median(Sample, Numbers) As MedianOfNumbers
FROM Sample
GROUP BY Groups

The message now tells me that I Median() isn't part of an aggregate
function. Are aggregate functions only avg, count, min, max, etc?

If my table is named "Sample", and I have one field named "Groups"
(which i want to group by) and another named "Numbers" (from which I
want the median).
I tried just using

SELECT Median(Table, Field)
FROM Table;

I get a Parameter box that pops up. Does that mean that my table name
and field name are not recognized?

Thanks for all the help.

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

When grouping you have to have an aggregate function around the Median()
function. Since the parameters are strings you have to delimit the
names of the table and column with quotes:

SELECT Groups, Sum(Median("Sample", "Numbers")) As MedianOfNumbers
FROM Sample
GROUP BY Groups

Use Sum or Avg or whichever aggregate function you want.

Since the Median() function uses DAO make sure you have the DAO library
checked in the References (VBA module, menu item Tools > References).
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkNXHoechKqOuFEgEQKt1QCfTnXlYlqErSHgK3UxsSpU83 LOEHgAoOCj
t/QY1K1AnXd/vTwXU+fHLlkw
=nG6W
-----END PGP SIGNATURE-----

ni*********@gmail.com wrote:

I hate to treat you as a personal tutor, but I still can't seem to get
this to work.

SELECT Sample.Groups, Median(Sample, Numbers) As MedianOfNumbers
FROM Sample
GROUP BY Groups

The message now tells me that I Median() isn't part of an aggregate
function. Are aggregate functions only avg, count, min, max, etc?

If my table is named "Sample", and I have one field named "Groups"
(which i want to group by) and another named "Numbers" (from which I
want the median).
I tried just using

SELECT Median(Table, Field)
FROM Table;

I get a Parameter box that pops up. Does that mean that my table name
and field name are not recognized?

Thanks for all the help.

[nick.vitone]

Thanks for all the help, I'm finally making some headway!

[jimfortune]

ni*********@gmail.com wrote:

Thanks for all the help, I'm finally making some headway!

Be sure to check out the method I posted earlier this month:

http://groups-beta.google.com/group/...2f7b4979f359ef

as an alternative to using the Median function. It's SQL only so it's
not as flexible as VBA, however it's more scalable :-). Note: When
pasting qryMedian from that post an extra '-' appears before the '<'
symbol that must be deleted.

Instead of:
SELECT Groups, Sum(Median("Sample", "Numbers")) As MedianOfNumbers FROM
Sample GROUP BY Groups

try this modification to MGFoster's SQL statement:
SELECT Groups, First(SELECT Median FROM qryMedian) As MedianOfNumbers
FROM Sample GROUP BY Groups

where:
qryRankForMedian:
SELECT Sample.Value, (SELECT Count(A.ID) FROM Sample AS A WHERE A.Value
< Sample.Value)+(SELECT Count(A.ID) FROM Sample AS A WHERE A.Value =
Sample.Value AND A.ID < Sample.ID)+1 AS Ranking, (SELECT Count(*) FROM
Sample)/2+0.5 AS WantRanking FROM Sample;

qryMedian:
SELECT Avg(qryRankForMedian.Value) AS Median FROM qryRankForMedian
WHERE (((Abs([Ranking]-[WantRanking]))<0.6));

Sample
ID Value Groups
1 1 A
2 2 A
3 3 A
4 4 B
5 5 B
6 5 B
7 5 C
8 6 C

The result looked like:
Groups MedianOfNumbers
A 4.5
B 4.5
C 4.5

If qryRankForMedian is changed to select only values within a group
(along with a suitable WHERE clause for WantRanking) it may be possible
to get the median of each group in its output line as well by using SQL
to reference the Groups value. To do that for the Median function
would require modification to accept the Groups value as an argument.
Of course the odds of someone needing this functionality are long. If
anyone needs that I'll try to flesh out the details.

That case would look like:
Groups MedianOfNumbers
A 2
B 5
C 5.5

James A. Fortune