Unique Value's in Query Issue

[tekari]

Hi,

I'm trying to run an access query to give me only unique values in a
query. This would seemingly be simple to me. I know that I can right
click and then hit properties and select "Unique Values". Which would
appear to work, but here is my problem...

When I run this query with "Unique Values" turned on without any
modification on a single column I end up getting about 366 unique
values.

Now if I take the same query and hit the "group by COUNT" button it
returns 455.

Am I just misunderstanding the functionality of COUNT? Shouldn't this
return the number of records given the parameters I set in the
criteria?

The only thing I can think of is that if I select "COUNT" that ACCESS
ignores the fact that I also have the parameters set to Unique Values?

Is there any way around this if thats even the issue?

Thanks in advance,
-Eric

 

[John Spencer]

In Access, Count will not count uniquely. It counts all the fields with
a value.

UNIQUE (DISTINCT) takes place AFTER the records to be returned have been
determined, Count takes place before Unique happens

If you want a count of unique values in Access you have to use two
queries or a nested subquery.

SELECT Count(*) as DistinctCount
FROM (SELECT Distinct SomeField FROM YourTable) as A

In your case, you could run a second query against your first query to
get the count.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================