?
Solved

Dcount unique

Posted on 2016-11-14
6
Medium Priority
?
75 Views
Last Modified: 2016-11-15
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*"));
0
Comment
Question by:pdvsa
6 Comments
 
LVL 54

Expert Comment

by:Ryan Chong
ID: 41887477
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
 
LVL 28

Expert Comment

by:MacroShadow
ID: 41887479
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
 
LVL 54

Accepted Solution

by:
Ryan Chong earned 2000 total points
ID: 41887480
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 19

Expert Comment

by:John Tsioumpris
ID: 41887484
The problem lies to qryOutstanding_2...you should have the query to return only unique values and then perform the Dcount....
0
 
LVL 58
ID: 41887533
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
 

Author Closing Comment

by:pdvsa
ID: 41887659
Perfect.  thank you.
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

864 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