Counting expression doesn't work when sorted by count - Access 200
- From: Pat <Pat@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2007 07:56:09 -0800
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
.
- Follow-Ups:
- Re: Counting expression doesn't work when sorted by count - Access 200
- From: Michel Walsh
- Re: Counting expression doesn't work when sorted by count - Access 200
- From: John Spencer
- RE: Counting expression doesn't work when sorted by count - Access 200
- From: KARL DEWEY
- Re: Counting expression doesn't work when sorted by count - Access 200
- Prev by Date: RE: if a field contains "vita" or "acid" , show "YES" to the result co
- Next by Date: RE: Counting expression doesn't work when sorted by count - Access 200
- Previous by thread: Re: Multiple Copies Query
- Next by thread: RE: Counting expression doesn't work when sorted by count - Access 200
- Index(es):
Relevant Pages
|
