Re: calculating a median within a query

jonm4102 wrote:
I'm trying to calculate the median of some numerical data. The data can
only be found in a query (henceforth query 1) field I previously made,
and I would prefer to calculate the median in a new query it without
making a table out of query 1. I can't find a median function in the
"Total" field, so is there so way to make an expression to calculate
the median of the orignial data from query 1 in my new query?

Also, what does name by string mean?

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

I've never seen the expression "name by string."

Here is a median finding query that I found on the web. It is set up
for T-SQL, but can be used in Access SQL; just change the names of the
tables/columns.

- -- From: http://www.aspfaq.com/show.asp?id=2506

- -- Find the median value

- -- For odd Count(*)

SELECT TOP 1 splunge FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) sub
ORDER BY splunge DESC



- -- For even Count(*). Can be used for odd Count(*) also.

SELECT AVG(splunge) FROM
(
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge
) sub_a
ORDER BY 1 DESC
) sub_1
UNION ALL
SELECT splunge FROM (
SELECT TOP 1 splunge = splunge * 1.0 FROM
(
SELECT TOP 50 PERCENT splunge
FROM blat ORDER BY splunge DESC
) sub_b
ORDER BY 1
) sub_2
) median


--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

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

iQA/AwUBRK69c4echKqOuFEgEQK17QCgqqpRlyjWPsDoyDV7KwOO5KI/oUAAoJLV
BH3e8tVeYy7JbacvZJMQfDG7
=cNR0
-----END PGP SIGNATURE-----
.

Relevant Pages

  • Group By Median: Custom Aggregate Function? Passing Array to SP?
    ... There is a good article at ASPFAQ on how to calculate median in a SQL ... SELECT TOP 50 PERCENT splunge ... FROM blat ORDER BY splunge DESC ...
    (comp.databases.ms-sqlserver)
  • Re: two columns leading nowhere
    ... suggested working with the median of the PK. ... Your main report would need two text boxes: ... query for the first query to 1 to the median then set the criteria of the ... second query set criteria for LastName to N to Z. ...
    (microsoft.public.access.reports)
  • Re: Calculat Median for each data group?
    ... when I run the query I get the following error: ... And just to be clear - I'm looking for a different median rate to be ... Function MedianF(pTable As String, ... Dim sglHold As Single ...
    (microsoft.public.access.queries)
  • Median from a Query
    ... sorts by original post date and not by activity date so this is a repost. ... module code posted in the Knowlege Base regarding median calculation. ... Perhaps the reason is that I am trying to get a median for values in a query? ... Anyway this results in having to store < in a separate field ...
    (microsoft.public.access.queries)
  • Re: How do I retrieve the median value of a field?
    ... assuming that the function actually works (which I won't vouch for since ... did you pass it two strings? ... and secondly the name of the field you want to generate the median on. ... median of the field for ALL the records in the table or a query. ...
    (microsoft.public.access.queries)