WorkUnit totals by month via Date range

[aldeb]

Below is the code I have for a query that shows me the number of WorkUnits 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 WorkUnit totals by month via a date range. If I put in the date range: Jan 1-April 30 I would like to get four WorkUnit totals instead of one. I would like to see the WorkUnit totals for each month. Can anyone assist with this? I asked a similar question like this yesterday but he SQL for this code is different and I have not been able to figure out how to apply yesterday's answer to this one. I have tried everything I can think of to no avail.

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;

 

[Michael J Ross]

Just thinking out loud here, but you could add a calculated field to your query like the following e.g.

Period: DatePart("m",[yourdatefield])

Group by that and count on your work units.

 

[aldeb]

Thanks Michael