TOTALS BY SHIFT & PERCENTAGE OF NO FAULTS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

TOTALS BY SHIFT & PERCENTAGE OF NO FAULTS

Author

Topic

aldeb
Starting Member

6 Posts

Posted - 2006-11-22 : 09:49:30

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]);

Someone else provided the following SQL but it is not working. I get this error:

The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numerica expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Select 'Shift 1', 
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] 
And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
and FirstShift = "yes"
Group By FaultCategory

union all Select 'Shift 2', 
FaultCategory, Count(*) As [Faults/NoFaults]
From WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] 
And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
and SecondShift = "yes"
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]);

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-11-22 : 11:01:19

Try changing
FirstShift = "yes" and SecondShift = "yes"
to
FirstShift = True and SecondShift = True

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 15:10:19

Also give this a shot

Select		'Shift 1', 
		FaultCategory,
		Count(*) As [Faults/NoFaults]
From		WorkUnitsFaultsMainTBL
Where		[TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
		and FirstShift = "yes"
Group By	FaultCategory

union all

Select		'Shift 2', 
		FaultCategory,
		Count(*)
From		WorkUnitsFaultsMainTBL
Where		[TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
		and SecondShift = "yes"
Group By	FaultCategory

UNION ALL

Select		'',
		'Total Work Units',
		sum(q.cnt)
from		(
			select		[WorkUnit],
					1 as cnt
			from		WorkUnitsFaultsMainTBL
			Where		[TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]
			group by	[WorkUnit]
		) q

Peter Larsson
Helsingborg, Sweden

aldeb
Starting Member

6 Posts

Posted - 2006-11-27 : 10:58:07

Peter,

I thought I replied to this but I do not see my reply. Would you please post your code again? It is garbled up with
a lot of extra characters when I copy paste. Also I do not understand what the 1 cnt is about. Thanks for your help.

aldeb
Starting Member

6 Posts

Posted - 2006-11-27 : 11:01:24

Sean,

I tried what you suggested and it changed nothing. Any other suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources