Select Distinct question - wrong results?

[dkmoreland]

I have a query which returns all the jobs that have an NCR associated with them:

SELECT DISTINCT [Non Conformance].[NCR#], [Production raw data].jobnum, [Production raw data].start_date, [Production raw data].end_date, [Production raw data].jobname
FROM [Non Conformance] INNER JOIN [Production raw data] ON [Non Conformance].[Job #] = [Production raw data].jobnum
WHERE ((([Production raw data].start_date) Between [Forms]![KPI data form]![TxtStartDate] And [Forms]![KPI data form]![TxtEndDate]));

This returns 9 records, which is correct. However, 3 of them have the same jobnum, which is also correct. I need to only count each job number once so I can display it on a form and am using the below this statement in a different query to do the count. The problem is that the result is totaling 6 instead of 7, which is what I think it should be. If I count manually and count only once the one with multiple occurrences, I come up with 7.

SELECT Count(*) AS ncrjobs
FROM (SELECT DISTINCT jobnum FROM [jobs_with_NCRs])  AS [Jobs_with_NCRs] ;

Does anybody see a hole in my logic or is my second query statement incorrect?

Before you mention it - I am aware that field names should not have special characters in them. This is an inherited problem that is taking forever to fix.

Thanks in advance for any and all input on this.

 

[arnelgp]

Can u group instead of distinct

 

[dkmoreland]

Can u group instead of distinct

I'm not sure what you mean - how will that give me a count of the number of unique values?

 

[isladogs]

SELECT [jobs_with_NCRs].jobnum, Count([jobs_with_NCRs].jobnum) AS [Jobs_with_NCRs]
FROM [jobs_with_NCRs]
GROUP BY [jobs_with_NCRs].jobnum;

 

[dkmoreland]

Awesome - the group by helped me to see that there were actually 2 jobs that had more than one ncr so my initial query was returning the right value after all.

Plus, I was able to see something else that I was doing wrong. Thanks very much. You guys rock.