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