Count Distinct - Alias!?

[Jay]

I'm slowly learning SQL and have been setting myself small
tasks/problems to solve as a way of doing so.

One of these was to count the unique values in one field. My learning
told me this would be a simple case of:

SELECT COUNT(DISTINCT FieldName)
FROM tblName

However as I'm sure you know it isn't. *Many* attempts and several
googles later I got:

SELECT COUNT(*)
FROM (SELECT DISTINCT FieldName FROM tblName)

which runs OK but when 'filtered' through the QBE grid gives:

SELECT COUNT(*) AS Expr1
FROM [SELECT DISTINCT(FieldName) FROM tblName]. AS[%$#@_Alias]

This is the first time I've seen this - what looks like a
dummy/temporary table in the QBE grid.

Can anyone tell me what's going on or point me to some good on-line info
about this.

Many thanks,

Jason

 

[MH]

Taking your comments in line:

I'm slowly learning SQL and have been setting myself small tasks/problems
to solve as a way of doing so.

One of these was to count the unique values in one field. My learning
told me this would be a simple case of:

SELECT COUNT(DISTINCT FieldName)
FROM tblName

That is the way to do it in SQL Server, unfortunately Access (Jet) does not
support this syntax.

However as I'm sure you know it isn't. *Many* attempts and several
googles later I got:

SELECT COUNT(*)
FROM (SELECT DISTINCT FieldName FROM tblName)

Yep, you have to build a query that selects distinct rows (this is the
subquery in brackets) and then count the rows.

which runs OK but when 'filtered' through the QBE grid gives:

SELECT COUNT(*) AS Expr1
FROM [SELECT DISTINCT(FieldName) FROM tblName]. AS[%$#@_Alias]

This is the first time I've seen this - what looks like a dummy/temporary
table in the QBE grid.

Can anyone tell me what's going on or point me to some good on-line info
about this.

I'm not sure what you are asking here, do you want to know about subqueries?

 

[Ken Snell \(MVP\)]

ACCESS changes the SQL statement from this

SELECT COUNT(*)
FROM (SELECT DISTINCT FieldName FROM tblName)

to this

SELECT COUNT(*) AS Expr1
FROM [SELECT DISTINCT(FieldName) FROM tblName]. AS[%$#@_Alias]

when you switch to the design view for the query. ACCESS wants to use the
"[ ]." structure when you go to the design view. So long as you don't have
any embedded [ ] characters in the "FROM" query's SQL statement, the query
will continue to work ok.

Moral of the story -- do not switch from SQL view to the design view.

 

[Chris2]

SELECT COUNT(*)
FROM (SELECT DISTINCT FieldName FROM tblName)

which runs OK but when 'filtered' through the QBE grid gives:

SELECT COUNT(*) AS Expr1
FROM [SELECT DISTINCT(FieldName) FROM tblName]. AS[%$#@_Alias]

This is the first time I've seen this - what looks like a
dummy/temporary table in the QBE grid.

Can anyone tell me what's going on or point me to some good on-line info
about this.

Jay,

MS Access will, on occasion, reformat your SQL. Sometimes this happens when switching
back and forth between Design View and SQL View, and sometimes happens after closing a
query and reopening it.

When it reformatted your SQL, it put brackets around your table-expression on the FROM
clause, replacing the (), and after the whole expression, it generated a table alias with
a random name.


Sincerely,

Chris O.

 

[Allen Browne]

Jay, if you just want a function to return the distinct count, see:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

If Ken's suggestion of a subquery is new, this might help:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

 

[Jay]

Jay, if you just want a function to return the distinct count, see:
Extended DCount()
at:
http://allenbrowne.com/ser-66.html

If Ken's suggestion of a subquery is new, this might help:
Subquery basics
at:
http://allenbrowne.com/subquery-01.html

Thank Everyone. I came across your subquery sections last night Allen
(nos. 1 and 2) and printed them both off. I'll be studying them on the
bus this morning. Thanks as well for the DCount pointer.

Regards,

Jason

 

[Michel Walsh]

The text of the query is less likely to be modified if you supply an alias
to the virtual table:


SELECT COUNT(*)
FROM (SELECT DISTINCT FieldName FROM tblName) AS a



Hoping it may help,
Vanderghast, Access MVP