UNIQUE WORK UNIT TOTALS 1

[netrusher]

I have the following SQL in a Total Query that shows me the total Faults by category and also gives me a total number of Work Units. Currently there are Five Faults and it also shows me there are Five Work Units. In reality there are only Three unique Work Units even though there are Five Faults. How can I modify the code below to give the Total of Unique Work Units instead of counting each Work Unit for each Fault?

Select FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION ALL Select  'Total Work Units', Count(*)
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt];

 

[AlexCuse]

have you tried

count(DISTINCT [work units field name])

in the second part of your union query?

I think this would achieve the desired result.

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[netrusher]

Thank you!

How and where would that fit into my current SQL?

 

[AlexCuse]

Oh wait, just realized that will only work with ANSI syntax enabled in a newer version of Access. I think you will need a subquery in your second part. Something like this:

UNION ALL Select 'Total Work Units', count([WorkUnits]) from (select distinct [Work Units Field] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])

Hope it helps,

Alex


A wise man once said
"The only thing normal about database guys is their tables".

 

[netrusher]

Thanks Alex