Top Ten issues from a query 1

[netrusher]

The below sql code give me totals of different System Group Faults found on our forklift trucks. What I would like to do is develop a top 10 or top 5 or top 3 from these totals that I can take into Excel as a chart. I know how to get the data to Excel but How can I just obtain the Top 10 etc. from the query. Can I do it with another Query based on this query? If so I do I just find the Top System Group Faults or just the Top Five, etc.?

Select SystemGroup,
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])
Group By SystemGroup

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

 

[netrusher]

Well since my first post did not get any answers, let me try and post differently. I currently have a query that produces results like the following:

SystemGroup Mechanical Totals
CWT 10
Fuel System 11
Hood 8
Hydraulic System 6
Levers 3
Mast 14
Mast-Assy-Fab 3
Mini Levers 2
OHG 3
Valve 8
OHG 7
Tires 3
Wheels 8

I want to then turn this into a Top 10 or Top 5 or Top 3 results so I can take to excel as a Top 10 or Top 5 or Top 3 Chart. I know how to get this to Excel but I do not know how to automatically do a query on these results that will make it just a Top 10 or Top 5 or Top 3. Any advice out there?

 

[PHV]

A starting point (SQL code):
SELECT TOP 10 SystemGroup, [Mechanical Totals]
FROM yourQuery
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[easyit]

[b]SELECT AliasName.* FROM
(
[/b]
Select SystemGroup,
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") AND (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And  ([TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt])
Group By SystemGroup

UNION ALL Select 'Total Work Units',
sum(IIf(FirstShift = Yes, 1, 0)) as [First Shift],
sum(IIf(SecondShift = Yes, 1, 0)) as [Second Shift],
Count(*) As [Totals]
From
(select distinct [WorkUnit], [FirstShift], [SecondShift] from WorkUnitsFaultsMainTBL
Where [TodaysDate] Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]);
[b]) AS Aliasname
[/b]

paste above in your query, then switch to designview in order to sort, selet TOP 5 etc.

EasyIT

"Do you think that’s air you're breathing?