Re: Counting expression doesn't work when sorted by count - Access
- From: Pat <Pat@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2007 08:56:00 -0800
Thanks Michel, works great. I now remember reading about this limitation in
passing a few months ago when I was just starting to learn Access. I agree,
it's an unwanted feature, seems like poor software design.
Patrick
"Michel Walsh" wrote:
In JET, ORDER BY does not accept alias, exception made for UNION query.
(don't ask me why, sounds like an unwanted feature to me, in non-union
queries, but that it is how it is...).
In Northwind:
SELECT Categories.CategoryName AS Cname
FROM Categories
ORDER BY Cname
Instead, try:
SELECT [Works_number] & [BT_SCP] & [Test_code_2] AS PSCP, Count([PSCP]) AS
Expr1
FROM Main
GROUP BY [Works_number] & [BT_SCP] & [Test_code_2]
ORDER BY Count([Works_number] & [BT_SCP] & [Test_code_2]) DESC;
which is cut_and_paste for your alias, carried over the ORDER BY clause.
Hoping it may help,
Vanderghast, Access MVP
"Pat" <Pat@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:4E1C60A2-863A-487B-9F0E-22F58514C9B1@xxxxxxxxxxxxxxxx
The following SQL script works fine:
SELECT [Works_number] & [BT_SCP] & [Test_code_2] AS PSCP, Count([PSCP]) AS
Expr1
FROM Main
GROUP BY [Works_number] & [BT_SCP] & [Test_code_2];
However the following SQL script doesn't work. The difference in the two
scripts is the added last line ORDER BY:
SELECT [Works_number] & [BT_SCP] & [Test_code_2] AS PSCP, Count([PSCP]) AS
Expr1
FROM Main
GROUP BY [Works_number] & [BT_SCP] & [Test_code_2]
ORDER BY Count([PSCP]) DESC;
When I try to run the second script, Access comes back with an 'Enter
parameter value' request for PSCP.
I rarely build my queries in SQL as I don't come from an SQL background.
I
usually build them in design view. In design view the first query is as
follows:
Field - PSCP: [Works_number] & [BT_SCP] & [Test_code_2]
Total - Group By
Field - Expr1: [PSCP]
Total - Count
and the second query is identical except for the sort, as follows:
Field - PSCP: [Works_number] & [BT_SCP] & [Test_code_2]
Total - Group By
Field - Expr1: [PSCP]
Total - Count
Sort - Descending
Hoping someone can point out my error. I'm guessing my problem lies in
incorrectly assuming the design grid is producing the SQL code I think it
should.
Thanks,
Pat
- References:
- Prev by Date: RE: Counting expression doesn't work when sorted by count - Access
- Next by Date: INSERT query - please help me.
- Previous by thread: Re: Counting expression doesn't work when sorted by count - Access 200
- Next by thread: INSERT query - please help me.
- Index(es):
Relevant Pages
|
