Distinct Count in Access query not counting distinct records.

Jarred Meyer
Jarred Meyer used Ask the Experts™
I have a very simple query I setup that displays a list of distinct OrderNumbers. It works fine like that but when I try to turn the query into a Count of the OrderNumbers it starts counting all the duplicates instead of what was being displayed with the DISTINCT function in there.

Here is what I have that displays the order number correctly;

SELECT DISTINCT dbo_View_JobProd.jpOrderNum
FROM dbo_View_JobProd
WHERE (((dbo_View_JobProd.jpJobNum)=[Forms]![frmJCSelector]![cboJobNum]));

Then I change it to a totals query and set OrderNum to be a count;

SELECT DISTINCT Count(dbo_View_JobProd.jpOrderNum) AS CountOfjpOrderNum
FROM dbo_View_JobProd
WHERE (((dbo_View_JobProd.jpJobNum)=[Forms]![frmJCSelector]![cboJobNum]));

The first query will return just one record (although there are 11 lines for that order the DISTINCT keeps it from displaying the OrderNum 11 times)

When I run the Totals query though it gives me 11..

Any idea what I'm doing wrong with that and how I could get it to do a distinct count?
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
try this

Select Count(A.jpOrderNum) as DistinctItemCount
SELECT  dbo_View_JobProd.jpOrderNum
FROM dbo_View_JobProd
WHERE (((dbo_View_JobProd.jpJobNum)=[Forms]![frmJCSelector]![cboJobNum]))
Group by  dbo_View_JobProd.jpOrderNum
) As A
Jarred MeyerProduction Manager


Very interesting.. I never had any idea that could be done! I used to stack queries on top of one another to accomplish what this is doing! Thanks for this and this will simplify so many other things for me!

Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial