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