TOTALS BY SHIFT AND TIME 1

[netrusher]

Everyone, I have been asked to do something that is way over my head. Below is the SQL of a query that I currently have. I need to throw some more criteria in for the totals. I have two Fields: FirstShift & SecondShift. They are yes/no boxes. I need the query to give me the same totals as it does now but by FirstShift & Secondshift. I also then would need the grand totals for the day which of course would be a combination of the two shifts. To take it a step further one of The FaultCategory's is NoFault. I would like to know the percentage of WorkUnits for each shift and total that were NoFaults. I know you need to divde the number of NoFaults by the Total Number of WorkUnits per shift and then per day. Is this doable? I sure hope so. I have been given this as a challenge and am hoping someone out there can get me the answer.

Thanks in advance.

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([WorkUnit]) from (select distinct [WorkUnit] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]);

 

[AlexCuse]

It should read:

count(a.*)

As far as your first question goes, try this:

Select FaultCategory, 
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
Group By FaultCategory

UNION Select 'TOTAL', 
sum(IIf(a.FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(a.SecondShift = Yes, 1, 0)) as [Second Shift],
Count(a.*) As [Totals]
From 
(select distinct [WorkUnit], [FirstShift], [SecondShift] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]) a

Basically, all you want to do is replace the table name with this query:

(select distinct [WorkUnit], [FirstShift], [SecondShift] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])

and give it an alias (a), and then refer to your derived table (using the alias) in the select statement.

Notice that because the derived table contains your where clause, there is no need to include that in your query.

Hope this helps,

Alex



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