DISTINCT option of an aggregate function in Access 2003

[nfrodsham]

I am trying to determine how to use the DISTINCT option of an aggregate
function as described in the Microsoft online help literature for
Access 2003:

"When you use an aggregate function, by default the summary information
includes all specified rows. In some instances, a result set includes
non-unique rows. You can filter out non-unique rows by using the
DISTINCT option of an aggregate function."

I cannot determine the correct syntax for this. I would like to
ideally count distinct values withing a grouping variable, for example:

SELECT userid, count(DISTINCT attribute) AS distinctattribute
FROM table
GROUP BY userid;

Is there any way to do this? Any help would be greatly appreciated.

Thanks.

 

[Van T. Dinh]

Count(DISTINCT ...) is not a valid syntax in JET SQL.

You need to use a SubQuery like:

SELECT UserID, Count([Attribute])
FROM
(
SELECT DISTINCT UserID, [Attribute]
FROM [YourTable]
)
GROUP BY UserID