Median in a query

[nick.vitone@gmail.com]

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]

Re: Median in a query

-----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_na me, field_name), <other columns>
FROM table_name
WHERE <criteria>
GROUP BY Median(table_na me, field_name) , <other columns>

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

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

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

iQA/AwUBQkHCjYechKq OuFEgEQLP0ACffv tQipID8zVlwFkn1 btHfr/+7n8An0OZ
c4p03mDiHN7jo28 XIOs3Y1VH
=seWN
-----END PGP SIGNATURE-----


nick.vitone@gma il.com wrote:[color=blue]
> 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?[/color]

[Brian Mullin via AccessMonster.com]

Re: Median in a query

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@gmail.com]

Re: Median in a query

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]

Re: Median in a query

nick.vitone@gma il.com wrote:[color=blue]
> 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.
>[/color]

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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

[nick.vitone@gmail.com]

Re: Median in a query

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]

Re: Median in a query

-----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("Sam ple", "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:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

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

iQA/AwUBQkNXHoechKq OuFEgEQKt1QCfTn XlYlqErSHgK3Uxs SpU83LOEHgAoOCj
t/QY1K1AnXd/vTwXU+fHLlkw
=nG6W
-----END PGP SIGNATURE-----

nick.vitone@gma il.com wrote:[color=blue]
> 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.
>[/color]

[nick.vitone@gmail.com]

Re: Median in a query

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

[jimfortune@compumarc.com]

Re: Median in a query

nick.vitone@gma il.com wrote:[color=blue]
> Thanks for all the help, I'm finally making some headway![/color]

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

http://groups-beta.google.com/group/comp.databases.ms-access/msg/1f2f7b4979f359ef

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("Sam ple", "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:
qryRankForMedia n:
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(qryRankForM edian.Value) AS Median FROM qryRankForMedia n
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 qryRankForMedia n 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