Dcount unique

Experts, I have a count in a query but need to count only the unique ID's.  How could I modify the following statement:  
DCount("[ID]","qryOutstanding_2") AS [Count]

thank you

sql:
SELECT qryBal2_Works.FacilityType, qryBal2_Works.Utilized, tblType_dropbox.Type, DCount("[ID]","qryOutstanding_2") AS [Count]
FROM qryBal2_Works INNER JOIN tblType_dropbox ON qryBal2_Works.FacilityType = tblType_dropbox.ID
WHERE (((tblType_dropbox.Type) Like "Working*"));
pdvsaProject financeAsked:
Who is Participating?
 
Ryan ChongConnect With a Mentor Commented:
typo for my comment, you can try this instead:
SELECT qryBal2_Works.FacilityType, qryBal2_Works.Utilized, tblType_dropbox.Type, 
(select count(*) from (select ID from qryOutstanding_2 group by ID)) AS [Count]
FROM qryBal2_Works INNER JOIN tblType_dropbox ON qryBal2_Works.FacilityType = tblType_dropbox.ID
WHERE (((tblType_dropbox.Type) Like "Working*"));

Open in new window

0
 
Ryan ChongCommented:
quick try:

SELECT qryBal2_Works.FacilityType, qryBal2_Works.Utilized, tblType_dropbox.Type, 
(select count(*) from (select ID from qryOutstanding_2 group by ID)) as  AS [Count]
FROM qryBal2_Works INNER JOIN tblType_dropbox ON qryBal2_Works.FacilityType = tblType_dropbox.ID
WHERE (((tblType_dropbox.Type) Like "Working*"));

Open in new window

0
 
MacroShadowCommented:
Use Allen Browne's ECount. ECount() offers an extra argument so you can count distinct values. The other arguments are the same as DCount().
0
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.

 
John TsioumprisSoftware & Systems EngineerCommented:
The problem lies to qryOutstanding_2...you should have the query to return only unique values and then perform the Dcount....
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
And just as an aside, you should not be using Domain functions inside a query as you are guaranteeing yourself poor performance (the query optimizer cannot cost it).   Use another query and join to it, or sub-selects as Ryan showed.

 All the Domain functions are nothing more than a SQL statement with a wrapper, so there's no reason not to write it directly in SQL.

 They were only intended to be used in places where SQL was not allowed.

 This is one of the main reasons you hear about Access apps that die after 5 or 6 users.

Jim.
0
 
pdvsaProject financeAuthor Commented:
Perfect.  thank you.
0
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.

All Courses

From novice to tech pro — start learning today.