How to find unique Work Unit Totals 2

[netrusher]

Below is the code I have that shows me how many leaks I have
per day. What I need now is to see how many Unique Work Units
I have per day. In the WorkUnitsFaultsMainTBL I have a
field called Work Units. Work Units can be listed multiple
times. I need to come up with a total of Unique Work Units
by day and show the results exactly like the code below
does but I am stumped as how to do this. All help will
be appreciated. It seems like I should be able to somehow
just change fields but I cannot pen it down to show only
unique Work Unit totals.

SELECT 
     Format(TodaysDate,'yyyy-mm-dd') AS [Date], 
     Count(*) AS [Leak Totals]
FROM 
     WorkUnitsFaultsMainTBL
WHERE 
     (((WorkUnitsFaultsMainTBL.Problem) Like "*leak*") 
AND 
     ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) 
AND 
     ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY 
     Format(TodaysDate,'yyyy-mm-dd')
ORDER BY 
     Format(TodaysDate,'yyyy-mm-dd'), Count(*) DESC;

 

[Golom]

Is this the sort of thing you're after?

SELECT [TodaysDate], 
       [WorkUnits], 
       Count(*) AS [Work Unit Totals]

FROM WorkUnitsFaultsMainTBL

WHERE [TodaysDate] Between [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                       And [Forms]![Queries_ReportsFRM]![EndDateTxt]) 
  AND [BuildID] In ("E010","C809","F001","C810","F187","A910","M173","M174")

GROUP BY [TodaysDate], [WorkUnits]

ORDER BY 1, 3 DESC

 

[netrusher]

Golom,

I used the code below and it is giving me every wu number
by date instead of a total of unique WorkUnits by date.

SELECT [TodaysDate], 
       [WorkUnit], 
       Count(*) AS [Work Unit Totals]

FROM WorkUnitsFaultsMainTBL

WHERE ([TodaysDate] Between [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                       And [Forms]![Queries_ReportsFRM]![EndDateTxt])
  AND [BuildID] In ("E010","C809","F001","C810","F187","A910","M173","M174")

GROUP BY [TodaysDate], [WorkUnit]

ORDER BY 1, 3 DESC

 

[Golom]

OK. I misunderstood your requirement

SELECT [TodaysDate], 
       Count (*) As [Work Unit Totals]

FROM (SELECT DISTINCT [TodaysDate], [WorkUnit] From WorkUnitsFaultsMainTBL
      WHERE  [TodaysDate] Between [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                              And [Forms]![Queries_ReportsFRM]![EndDateTxt])
        AND  [BuildID] In ("E010","C809","F001","C810","F187","A910","M173","M174")) As W

GROUP BY [TodaysDate]

ORDER BY 1, 2 DESC

 

[netrusher]

Golom,

I get the follow error when running the above code:

Syntax error in FROM clause

 

[lespaul]

extra paren, try this:

SELECT [TodaysDate],
       Count (*) As [Work Unit Totals]

FROM (SELECT DISTINCT [TodaysDate], [WorkUnit] From WorkUnitsFaultsMainTBL
      WHERE  [TodaysDate] Between [Forms]![Queries_ReportsFRM]![StartDateTxt]
                              And [Forms]![Queries_ReportsFRM]![EndDateTxt]
        AND  [BuildID] In ("E010","C809","F001","C810","F187","A910","M173","M174")) As W

GROUP BY [TodaysDate]

ORDER BY 1, 2 DESC

Leslie

Have you met Hardy Heron?

 

[Golom]

Get rid of the extra paren.

SELECT [TodaysDate], 
       Count (*) As [Work Unit Totals]

FROM (SELECT DISTINCT [TodaysDate], [WorkUnit] From WorkUnitsFaultsMainTBL
      WHERE  [TodaysDate] Between [Forms]![Queries_ReportsFRM]![StartDateTxt] 
                              And [Forms]![Queries_ReportsFRM]![EndDateTxt][COLOR=black yellow][s])[/s][/color]
        AND  [BuildID] In ("E010","C809","F001","C810","F187","A910","M173","M174")) As W

GROUP BY [TodaysDate]

ORDER BY 1, 2 DESC

 

[netrusher]

Golom and LesPaul,

Thanks for the help. That did it!

 

[Golom]

Thanks Leslie ... beat me to it.