DISTINCT option of an aggregate function in Access 2003

[nfrodsham]

In Microsoft's help literature, it states:

"You can filter out non-unique rows by using the DISTINCT option of an
aggregate function"

I am trying to do this in Access 2003 with the COUNT aggregate
function, but there is no reference, at least that I can find anywhere,
of how to do this. I have multiple lines fields for which I would like
to do a "count distinct", but for simplicity, I am showing an example
of only one field. Here is my SQL line:

SELECT [fishing data].userid, COUNT ([fishing data].browseragent) AS
CountOfbrowseragent
FROM [fishing data]
GROUP BY [fishing data].userid;

I have tried putting "DISTINCT" after COUNT, after the parenthesis
immediately following "COUNT" and various other forms:

COUNT DISTINCT ([fishing...
COUNT (DISTINCT [fishing data...
COUNT (DISTINCT ([fishing data...)
COUNT ([DISTINCT] [fishing data...

I cannot figure out the proper syntax. I have read through other posts
about users frustrated with inability to "count distinct" in Access,
but I thought that maybe this is an aggregate function option as stated
in the Microsoft help. I have been racking my brain with this. Any
assistance would be greatly appreciated!

[MGFoster]

nf*******@gmail.com wrote:

In Microsoft's help literature, it states:

"You can filter out non-unique rows by using the DISTINCT option of an
aggregate function"

I am trying to do this in Access 2003 with the COUNT aggregate
function, but there is no reference, at least that I can find anywhere,
of how to do this. I have multiple lines fields for which I would like
to do a "count distinct", but for simplicity, I am showing an example
of only one field. Here is my SQL line:

SELECT [fishing data].userid, COUNT ([fishing data].browseragent) AS
CountOfbrowseragent
FROM [fishing data]
GROUP BY [fishing data].userid;

I have tried putting "DISTINCT" after COUNT, after the parenthesis
immediately following "COUNT" and various other forms:

COUNT DISTINCT ([fishing...
COUNT (DISTINCT [fishing data...
COUNT (DISTINCT ([fishing data...)
COUNT ([DISTINCT] [fishing data...

I cannot figure out the proper syntax. I have read through other posts
about users frustrated with inability to "count distinct" in Access,
but I thought that maybe this is an aggregate function option as stated
in the Microsoft help. I have been racking my brain with this. Any
assistance would be greatly appreciated!

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Access (JET) doesn't support the Count(Distinct <column>) function. The
Help article was about using DISTINCT like this:

SELECT DISTINCT <column list> ...
FROM ... etc. ...

There are various postings on this newsgroup about this problem. Search
Google groups for another answer. Or, perhaps this simple solution:

SELECT userid, Count(*) As BrowserAgentCount
FROM (SELECT userid, browseragent
FROM [fishing data]
GROUP BY userid, browseragent)
GROUP BY userid
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQnkxwIechKqOuFEgEQJBoACeKmriKMRO8bU9uFcpmbcrts rlm6EAn2cO
JtExwhvYCPf4gGpxJmyl7yiR
=FKwa
-----END PGP SIGNATURE-----