Faults by month via date range 2

[netrusher]

Below is the code I have for a query that shows me the number of faults found on a WorkUnit via a date range that is put in via calendars. This works great. It gives me one total for the date range. What I would like to be able to do is see the fault totals by month via a date range. If I put in the date range: Jan 1-April 30 I would like to get four fault totals instead of one. I would like to see the fault totals for each month. Can anyone assist with this?

SELECT Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("D024","C879","E010","C809","F001","C810","F187","A910","M173","M174","G004","E818","N005","F813")))
ORDER BY Count(*) DESC;

 

[PHV]

what about this ?
SELECT Format(TodaysDate,'yyyy-mm') AS [Month], Count(*) AS FaultTotals
FROM WorkUnitsFaultsMainTBL
WHERE FaultCategory)<>'No Faults'
AND (TodaysDate Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)
AND BuildID In ('D024','C879','E010','C809','F001','C810','F187','A910','M173','M174','G004','E818','N005','F813')
GROUP BY Format(TodaysDate,'yyyy-mm')
ORDER BY Count(*) DESC;

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

This maybe...

SELECT Count(*) AS FaultTotals, Format(TodaysDate, "yyyy-mm")

FROM WorkUnitsFaultsMainTBL

WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults")
  And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt)
  And ((WorkUnitsFaultsMainTBL.BuildID) In ("D024","C879","E010","C809","F001","C810","F187","A910","M173","M174","G004","E818","N005","F813")))

Group By Format(TodaysDate, "yyyy-mm")

ORDER BY Count(*) DESC;

Skip,

_[red][/red]

 

[netrusher]

PH & SKIP,

They both worked and I thank you! Below is the code for
another query I have and I want to get these WorkUnits
totals by month. I have tried in vain to apply the same
code you all sent. I have manipulated it around but I
cannot seem to get it right. Could you see if you can help
me get the same results with this query? Thanks for your
help.

SELECT 'Total Work Units' AS FaultCategory, Count([WorkUnit]) AS [WU Totals]
FROM [Select Distinct [WorkUnit]
         FROM WorkUnitsFaultsMainTBL
            WHERE BuildID IN ("G004","E818","N005","F813","D024","C879") AND
            PossibleCause NOT IN ("Out of Stock") AND
                [TodaysDate] BETWEEN [Forms]![Queries_ReportsFRM]![StartDateTxt] AND
                                        [Forms]![Queries_ReportsFRM]![EndDateTxt]]. AS vTbl;