Not counting duplicate records

[BPhilb]

I have a query where I am trying to count errors from two different fields. The Error Type field should count everything, but the order number field should only count unique order numbers. Right now the fields are coming out the same. Can anyone offer me any advice? Thanks in advance.

My SQL Statement as of right now is:

SELECT SWErrors.[Picker Id], Count(SWErrors.[Error Type]) AS [CountOfError Type], Count(SWErrors.[Order Number]) AS OrderErrors
FROM SWErrors
GROUP BY SWErrors.[Picker Id];

 

[tlbroadbent]

Use Count(Distinct ColName) Terry L. Broadbent - DBA

http://tlbroadbent.home.attbi.com/index.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers."

 

[BPhilb]

Thanks for the response. Will this work in Access 2000?

 

[tlbroadbent]

Sorry. Count Distinct doesn't work in MS Access. It does work in T-SQL which I use more frequently than Jet SQL. The following query will work in Access. As you can see, it uses sub-queries.

SELECT
a.[Picker Id],
a.TotalErrors,
b.OrderErrors
FROM

(SELECT [Picker Id], Count(*) AS TotalErrors
FROM SWErrors GROUP BY [Picker Id]) AS a

INNER JOIN

(SELECT [Picker Id], Count(*) AS OrderErrors
FROM

(SELECT DISTINCT
[Picker Id],
[Order Number]
FROM SWErrors) AS c
GROUP BY [Picker Id]) AS b

ON a.[Picker Id]=b.[Picker Id] Terry L. Broadbent - DBA

http://tlbroadbent.home.attbi.com/index.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers."