Counting expression doesn't work when sorted by count - Access 200

P

Pat

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
 
K

KARL DEWEY

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];
 
J

John Spencer

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;

The Order By clause DOES NOT KNOW what is in the SELECT clause and therefore
it can't use the alias PSCP.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

Michel Walsh

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
 
P

Pat

Thanks Karl. Your script works well, gives the same result as my first
script, and also works when I sort it.

Do you know why my first script works fine until I try to sort it?

Also could you please elaborate on how my query attempts to display
individual records and the total count at the same time. Any clarification
would be great.

Much appreciated,
Pat

KARL DEWEY said:
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 said:
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
Click to expand...
Click to expand...
 
P

Pat

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 said:
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 said:
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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

select query row empty if value zero/null 1
group by and count null values 6
count from multiple tables 3
Counting records matching criteria 4
Why is my Top Value query not working right? 1
Group by Range in Crosstab Query 4
age groups 6
Count Query Over Multiple Fields 1

Top