Use of Count in query

[imellor]

I have created a query in design view which has generated this SQL:

SELECT [ACD-C-DataSourceExport].Field2
FROM [ACD-C-DataSourceExport]
GROUP BY [ACD-C-DataSourceExport].Field2
HAVING ((([ACD-C-DataSourceExport].Field2) Like "2313*"));

This works fine and returns all 265 records bginning 2313

However if I change (in design view) from Group by to Count to produce this SQL, it returns nothing, just a blank result:

SELECT Count([ACD-C-DataSourceExport].Field2) AS CountOfField2
FROM [ACD-C-DataSourceExport]
HAVING (((Count([ACD-C-DataSourceExport].Field2)) Like "2313*"));

I thought it would return 265 (ie the number of records that match the criteria

Can anyone suggest what I am doing wrong?

Thank you

Ian

 

[rzw0wr]

Try,
SELECT COUNT([ACD-C-DataSourceExport].Field2)
FROM [ACD-C-DataSourceExport]
WHERE ((([ACD-C-DataSourceExport].Field2) Like "2313*"))
;


http://www.w3schools.com/sql/sql_func_count.asp
Dale

 

[imellor]

Dale

Thank you, as you have probably guessed I'm new to access.
Your SQL brings up something I didn't expect in design view, I think I will probably be better off concentrating on writing SGL!!!

Thank you

Ian

 

[rzw0wr]

Like what did it bring up?
And what is SGL?

Dale

 

[rzw0wr]

Try this.

SELECT Count([ACD-C-DataSourceExport].Field2) AS MyCount
FROM [ACD-C-DataSourceExport]
GROUP BY [ACD-C-DataSourceExport].Field2
HAVING ((([ACD-C-DataSourceExport].Field2) Like "2313*"));

Dale

 

[imellor]

Like what did it bring up?
And what is SGL?

Dale

Just that it is in two columns
Sorry, I meant SQL

Ian

 

[Brianwarnock]

The Where is an aggregate function and as you are using another , Count , the design grid uses two columns, the Where column normally has the show deselected.

Criteria supplied by Where takes place before grouping Having after.

Brian

 

[imellor]

Thank you, I can see the logic now, but initially, it was not what I expected.