VBA DCount unique values in Access 2007

I have a DCount function that counts a particular people field in a table that satisfy some criteria. It returns the total number of records in that table. To clarify (!?), there are three records with "fred" in that people field, two with "bill". The Dcount return 5, ie all the Freds and the Bills. I want it to tell me that there are 2 different people, regardless that there are three instances of Fred and two of Bill. So I am looking for a count of unique values.

Can this be done in DCount?

Many thanks.
PeborghAsked:
Who is Participating?
 
Patrick MatthewsConnect With a Mentor Commented:
I cover getting distinct counts in Access in my article http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_2417-Calculating-Distinct-Counts-in-Access.html


For example, to get distinct counts with no criteria:


SELECT Count(y.People) AS DistinctCount
FROM
    (SELECT DISTINCT x.People
    FROM SomeTable x) AS y

Open in new window



With criteria:

SELECT Count(y.People) AS DistinctCount
FROM
    (SELECT DISTINCT x.People
    FROM SomeTable x
    WHERE Status = "Active" And AsOfDate >= #1/1/2008#) AS y

Open in new window

0
 
peter57rCommented:
You cannot do this just in a Dcount().
You need a query to get the distinct values first and then you can count them.

For example..  qryNames =

Select distinct fullname from mytable

Then you can use
Dcount("*", "qryNames")
0
 
PeborghAuthor Commented:
matthewspatrick,

You have just what I wanted: DcountDistinct. I will try it before week's end and let you know.

Thank you,

peter
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Patrick MatthewsCommented:
That would work too :)
0
 
PeborghAuthor Commented:
matthewspatrick,

I am getting #Error out of DCountDistinct.

I then noticed that it required a reference to DAO (3.6 in my case). When I try to add this to references, I get name conflicts and the reference does not install. Funny because my app had DOA.recordsets all over the place before DCountDistinct ever arrived.

Btw, If I remove the "DAO." from your recordset declaration, that makes no difference either to the #Error.

Any thoughts?

Thanks,

peter
0
 
Patrick MatthewsCommented:
Peter,

Can you post a sample file?  Please use the MDB format if you do--the computer I have with me has Access 2003.

Patrick
0
 
PeborghAuthor Commented:
Very difficult as the whole thing is rather complex (yes I know!). Can you venture any suggestions without the actual code?

Thanks,

peter
0
 
peter57rCommented:
You must leave the DAO.  prefixes in place.

A2007 doesn't use DAO3.6  -  it uses the Access database engine instead.
You will already have a reference set for that.  You do not need the DAO3.6 library.

You should create a query which does ONLY the "select Distinct...." part first (the bit in brackets) and make sure that is working.  Then you can create a new query using the first query which counts the records.

If you still have issues you should post the sql of the query(ies) you are using.
0
 
PeborghAuthor Commented:
matthewspatrick,

Problem solved. The #ERROR was because I had the multi-word name of the table without []s. In my ordinary Dcount it accepted "blah de blah". DCountDistinct required "[blah de blah]".

Now all is well... Thanks,

peter
0
 
PeborghAuthor Commented:
the DCountDistinct worked fine, once I had the table name in brackets...
0
All Courses

From novice to tech pro — start learning today.