I have a select query that groups records together, specifically
baseball players and their home runs and at-bats. I want to create a
query that displays [HR's], [AB's], and [HR's per AB's] and that sorts
ASCENDING on the [HR's per AB's] field.

My field is:

AB_per_HR: [AB]/[HR]

If I set the SORT to Ascending, I get an error message saying I tried
to execute a query that does not include the specified expression as
part of an aggregate function.

If I leave the TOTAL and SORT specifications empty, then the query
works, but does not display in sorted order on the HR.

How do I get the query to perform the divide function AND display the
results sorting on the field that does the divide?

Thank you.




---
Allen

Otie wrote:
order By [HR's per AB's]
or does it looke like
Order By [HR]/[AB]

The second one should work.

It looks like 'Order By [HR]/[AB]', except that it is [AB]/[HR].

I solved my problem by making another query referecing the first
query, telling the second query to sort on the data I want. Apparently
I could not do it with just one query.



---
Allen


"Salad" <oil (AT) vinegar (DOT) com> wrote

Did you get an answer yet? How about feeding the data into a report, which
would have a textbox doing the calc yyou want..

Alan


"Otie" <otie_nospam (AT) adelphia (DOT) net> wrote

"Alan Stein" <stickys (AT) bellsouth (DOT) net> wrote in
news:9OoUf.527$yn4.130 (AT) bignews7 (DOT) bellsouth.net:

in the ORDER BY clause, use the ordinal field numbers, something like
this:

select player, [hr],[ab],[ab]/[hr] as AB_per_HR
from ...
ORDER BY 4 desc, 1

But by your description, your query probably looks like this:

select player, sum(hr) as HR, sum(ab) as ab
from ...
group by player


....and you want something like this:

select player, sum(hr) as hr, sum(ab) as ab, sum(hr)/sum(ab) as hr_rate
from ...
group by player
order by 4,1

I changed the ratio around, because you don't want to have a divide-by-
zero (can't have an HR w/o an at-bat...).

If my memory serves me well, you can not sort on a field that is an
expression. You are asking it to sort by a field that does not have a value
when the query first executes.

The simplest thing I could suggest is to make your query a 'make table
query', then just do your sorting an manipulation of data using the newly
made table. Hope this helps.



"Alan Stein" <stickys (AT) bellsouth (DOT) net> wrote