calculating a median within a query

[jonm4102]

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?

Thanks.

[MGFoster]

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 <atearthlink <decimal-pointnet
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/AwUBRK69c4echKqOuFEgEQK17QCgqqpRlyjWPsDoyDV7KwOO5K I/oUAAoJLV
BH3e8tVeYy7JbacvZJMQfDG7
=cNR0
-----END PGP SIGNATURE-----

[Lyle Fairfield]

jonm4102 wrote:

I'm trying to calculate the median of some numerical data ....

My almost totally untested effort:

Dim s(2) As String

s(0) = "SELECT TOP 1 sq1.Score AS Median FROM"
s(0) = s(0) & vbNewLine
s(0) = s(0) & "[SELECT TOP 50 PERCENT Score FROM Query3 ORDER BY Score
ASC]. sq1"
s(0) = s(0) & vbNewLine
s(0) = s(0) & "ORDER BY sq1.Score DESC"

s(1) = Replace(s(0), "ORDER BY Score ASC", "ORDER BY Score DESC")
s(1) = Replace(s(1), "ORDER BY sq1.Score DESC", "ORDER BY sq1.Score
ASC")

With CurrentProject.Connection
Debug.Print _
(.Execute(s(0)).Collect("Median") + _
.Execute(s(1)).Collect("Median")) / 2
End With

[CDMAPoster]

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?

Thanks.

I use SQL for finding medians. See:

http://groups.google.com/group/comp....b6eb508f22fa9d

Click back a link or two to find SQL for the median of non-grouped
data.

I hope this helps,

James A. Fortune
CD********@FortuneJames.com

[jonm4102]

Thanks for your previous post. I've run into another hurdle, and would
greatly appreciate any help you may be able to offer. I have revenue
data sorted by month and years for a number of distinctly labeled
stores (imagine a Center ID). I would like to find the percentage of
"gainers" (stores whose monthly revenues exceed the previous month's
revenues) out of the all the stores. I know how to determine this by
using an individual queries to sort out each store's revenue by year
and month, and then use another query to classify gainers v. decliners,
and then (Fnally!) calculate the percentages. Is there an easier way to
do this than to run around 50 queries (one for each month for 4 years)?
Thanks.

Jonathan


CD********@FortuneJames.com wrote:

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?

Thanks.

I use SQL for finding medians. See:

http://groups.google.com/group/comp....b6eb508f22fa9d

Click back a link or two to find SQL for the median of non-grouped
data.

I hope this helps,

James A. Fortune
CD********@FortuneJames.com

[CDMAPoster]

jonm4102 wrote:

Thanks for your previous post. I've run into another hurdle, and would
greatly appreciate any help you may be able to offer. I have revenue
data sorted by month and years for a number of distinctly labeled
stores (imagine a Center ID). I would like to find the percentage of
"gainers" (stores whose monthly revenues exceed the previous month's
revenues) out of the all the stores. I know how to determine this by
using an individual queries to sort out each store's revenue by year
and month, and then use another query to classify gainers v. decliners,
and then (Fnally!) calculate the percentages. Is there an easier way to
do this than to run around 50 queries (one for each month for 4 years)?
Thanks.

Jonathan


CD********@FortuneJames.com wrote:

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?
>
Thanks.

I use SQL for finding medians. See:

http://groups.google.com/group/comp....b6eb508f22fa9d

Click back a link or two to find SQL for the median of non-grouped
data.

I hope this helps,

James A. Fortune
CD********@FortuneJames.com

Jonathan,

Your problem seems to be getting away from median calculations. Try
the following thread:

http://groups.google.com/group/comp....bdfadf979aa742

Maybe you can curve-fit the slope for a period of several years (the 48
monthly medians for each Center ID?) all at once and use the sign of
the slope to determine gainers and losers. You're still going to have
to do a lot of work and you also have to do the percentage calculation
so any shortcuts have to include those steps. It's all possible in
SQL, but also consider doing everything in VBA because of the amount of
sequential calculation required. The percentage part is not difficult
in SQL because you can sum IIf([Gainer], 1, 0) divided by the number of
query records (another sum) to get the fraction of gainers. So the SQL
approach would need a query to get the medians, a query to get the
slopes and a query to get the percentages. It's possible that a
crosstab query can handle all this at once but for such a complex query
I'd want to see the results of the three individuals to cross-check the
results (no pun intended). Even a second totals query on the crosstab
wouldn't be too bad. The Sgn() function can be used on the slope
calculation to return a -1, 0 or 1 for the Value (Gainer) field in the
(wide) crosstab. The more I think about it, the better three separate
queries look.

James A. Fortune
CD********@FortuneJames.com

The bedraggled young woman showed up at the steps of a convent. The
convent took the woman in and had a priest talk to her. "How did you
come to such a sad state of affairs?" he asked. "I started out snow
white," she replied, "but then drifted."