Re: calculating a median within a query

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




CDMAPoster@xxxxxxxxxxxxxxxx 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.databases.ms-access/msg/7fb6eb508f22fa9d

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

I hope this helps,

James A. Fortune
CDMAPoster@xxxxxxxxxxxxxxxx

Jonathan,

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

http://groups.google.com/group/comp.databases.ms-access/browse_frm/thread/864c89cf2a74193d/7cbdfadf979aa742#7cbdfadf979aa742

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
CDMAPoster@xxxxxxxxxxxxxxxx

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."

.

Relevant Pages

  • Re: calculating a median within a query
    ... stores. ... 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, ... I can't find a median function in the ...
    (comp.databases.ms-access)
  • Re: Break a list to four smaller list
    ... You need to read a book on SQL and RDBMS. ... I have a whole chapter on the various ways to computer a Median in my ... statistical median, whose value must be a member of the set, and the ... query, which finds the median weight of parts from the famous Chrs Date ...
    (microsoft.public.sqlserver.programming)
  • 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)
Loading