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 5 1740
Otie wrote: 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
Does your OrderBy statement look like
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" <oi*@vinegar.com> wrote in message
news:k_****************@newsread2.news.pas.earthli nk.net... Otie wrote: 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
Does your OrderBy statement look like order By [HR's per AB's] or does it looke like Order By [HR]/[AB]
The second one should work.
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" <ot*********@adelphia.net> wrote in message
news:xp********************@adelphia.com... 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
"Alan Stein" <st*****@bellsouth.net> wrote in
news:9O***************@bignews7.bellsouth.net: 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" <ot*********@adelphia.net> wrote in message news:xp********************@adelphia.com...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
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" <st*****@bellsouth.net> wrote in message
news:9O***************@bignews7.bellsouth.net... 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" <ot*********@adelphia.net> wrote in message news:xp********************@adelphia.com...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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: euang |
last post by:
Hi,
I have been using access 2000 for two years on WINDOWS NT to display dynamic
aweb page using ASP
My ISP has now changed to Windows 2003, and I am having major problems
displaying...
|
by: longtim |
last post by:
I have been having endless difficulty creating reports/queries that
set any relevent parameters from controls in forms.
I am creating an application under access 2003 but will target access...
|
by: brian kaufmann |
last post by:
Hi,
I had sent this earlier, and would appreciate any suggestions on this.
I need to make calculations for unemployment rate for three different
data sources (A,B,C) for many countries and age...
|
by: Joe |
last post by:
I am using Access 2003 and are linking to an Oracle 9i ODBC datasource
(using Oracle ODBC drivers). After linking the tables in Access, I inspect
the data contained in the linked tables. For...
|
by: tlyczko |
last post by:
Hello,
I have read about currency calculations, etc. in this newsgroup, and I
understand that currency math will be sufficiently accurate for what I
need to do for a mileage/expense report.
...
|
by: John Øllgård Jensen |
last post by:
Hi
Using MS Asccess 2000:
In a query I'm trying to create a new field with following expression:
FilmDate: Left(,4)
The field "FilmNo" is another text field in the query.
This is...
|
by: debi.robarts |
last post by:
Ok, in my database I have something like this:
Date One
# of Days to Next Date
These calculate a field "Date Two."
Because "Date Two" is an expression (calculated by the form), I can't
set...
|
by: matthew.newsome |
last post by:
I have two fields on a query, one is a count and one is either Red,
Amber or Green
So my results are
Red 5
Amber 7
Green 2
I need to calculate the count of Reds (5) by a...
|
by: EManning |
last post by:
I'm using A2003 connected to a SQL 2000 backend. This is not an adp.
I have a table which I store 0 and -1 for 2 bit fields. I have a
listbox on a form based on a query of this table. The...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
| |