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
Then I change it to a totals query and set OrderNum to be a count;
SELECT DISTINCT Count(dbo_View_JobProd.jpOrderNum) AS CountOfjpOrderNum
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?