distinct value on the total field

[99mel]

I want to select distinct count instead of count in the total field in a select query.

Can this no be done?

 

[dalchri]

Try:

SELECT COUNT(*) AS DistinctCount
FROM (
SELECT DISTINCT FieldName
FROM YourTable
) AS DistinctList

The subquery determines the distinct list of values in the field. The main query then counts the number of values in that list.

 

[99mel]

i've got the following sql query

select count(distinct fielda)
from tablea

this brings back an error!

 

[amcg]

Until they provide us with count(distinct [fieldname]), then in Access 97 at any rate, your best bet might honestly be to split it up into two queries - the first selecting the distinct values and the second using the first as a source.

An inelegant solution, but it would get you moving along...

 

[99mel]

they dnt allow it in Access 2000 either? thats what i'm using.