Obtain Unique Work Units via SQL

[aldeb]

Below is the SQL I have for a Query. The results of my query are below the code. Everything works great except for the WorkUnit totals.
I am getting the total Records for the date range and I really need the Unique workUnits for the date range. For this particular daterange I
have a total of 197 WorkUnits but in reality there really are only 89 Unique WorkUnits.
In the example below on one date range I had 0 No Faults and on the other date range I had 7 No Faults.

Can anyone tell me how to add to this query to get only
Unique WorkUnits?


SELECT 'No Faults' AS [Fault Type], Sum(IIf([FaultCategory]='No Faults',1,0)) AS [NoFault Totals],
Count (WorkUnitsFaultsMainTBL.WorkUnit) AS [Total Work Units],
FormatPercent(Sum(IIf([FaultCategory]='No Faults',1,0))/Count([WorkUnit]),2) AS [Pct NoFaults]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174"))
And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt));




Fault Type--------NoFault Totals----------Total Work Units
No Faults-----------------0--------------------------197

Fault Type--------NoFault Totals----------Total Work Units
No Faults-----------------7--------------------------197

[stanl]

Have you tried adding the word DISTINCT after SELECT? Stan

[aldeb]

Stan,

Yes I have and I still get the 197 total.

[geekgirlau]

Instead of counting the Work Units, group by them instead. Then create another query based on this new sub-query, and then count the Work Units.

[asingh]

Hi,
Why dont you create a select query that will just group by workunits...and also count them...the GROUP BY operator will create a distinct and unique list.....and also show the respective count for each of the unique workunits....it would be something like this..

SELECT WorkUnit Count(WorkUnit) AS WorkUnit
FROM WorkUnitsFaultsMainTBL
GROUP BY WorkUnit;

regards,

asingh

[asingh]

apologies for missing out on a COMMA:

SELECT WorkUnit, Count(WorkUnit) AS WorkUnit
FROM WorkUnitsFaultsMainTBL
GROUP BY WorkUnit;