Improve company productivity with a Business Account.Sign Up

x
?
Solved

VBA DCount unique values in Access 2007

Posted on 2011-02-22
10
Medium Priority
?
1,095 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:Peborgh
  • 5
  • 3
  • 2
10 Comments
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 34950321
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
 
LVL 77

Expert Comment

by:peter57r
ID: 34950330
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
 

Author Comment

by:Peborgh
ID: 34950610
matthewspatrick,

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

Thank you,

peter
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34950921
That would work too :)
0
 

Author Comment

by:Peborgh
ID: 34953095
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 34954042
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
 

Author Comment

by:Peborgh
ID: 34959017
Very difficult as the whole thing is rather complex (yes I know!). Can you venture any suggestions without the actual code?

Thanks,

peter
0
 
LVL 77

Expert Comment

by:peter57r
ID: 34959223
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
 

Author Comment

by:Peborgh
ID: 34971422
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
 

Author Closing Comment

by:Peborgh
ID: 35009087
the DCountDistinct worked fine, once I had the table name in brackets...
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

579 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question