RE: Counting expression doesn't work when sorted by count - Access 200
- From: KARL DEWEY <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 29 Nov 2007 08:10:00 -0800
You can not display individual records and the total count at the same time.
Try this ---
SELECT Count([Works_number] & [BT_SCP] & [Test_code_2]) AS PSCP_Count
FROM Main
GROUP BY [Works_number] & [BT_SCP] & [Test_code_2];
--
KARL DEWEY
Build a little - Test a little
"Pat" wrote:
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:
- References:
- Prev by Date: Counting expression doesn't work when sorted by count - Access 200
- Next by Date: Re: SQL String
- Previous by thread: Counting expression doesn't work when sorted by count - Access 200
- Next by thread: RE: Counting expression doesn't work when sorted by count - Access
- Index(es):
Relevant Pages
|
Loading
