Top 10 filtering

[Chimp8471]

I have generated a query which the data is then displayed in a report.....the trouble is that i am dealing with approximatly 1000 records per day....

I am trying to display just the top 10 event for a specific day.

so for example:

Line A had 500 minutes downtime
Line B had 475 minutes
etc...

but this would continue onwards i just want the 10 highest to be displayed,

Can this be done ??? if so how

Cheers

Andy

 

[Mile-O]

Change the fields in the SQL:

SELECT TOP 10 tblMyTable.MyField
FROM tblMyTable
GROUP BY tblMyTable.MyField
ORDER BY tblMyTable.MyField DESC;

 

[Chimp8471]

Hi Thanks again for the help..

this is the sql currently in place...

SELECT tblEvents.DayCode, tblEvents.Line, tblEvents.EventCode, tblEvents.MinorStop, tblEvents.MajorStop, tblEvents.Breakdowns, [MinorStop]+[MajorStop]+[Breakdowns]+[CIP]+[ProductChange]+[Maintenance] AS total
FROM tblEvents INNER JOIN tblMachines ON tblEvents.Line = tblMachines.Line
GROUP BY tblEvents.DayCode, tblEvents.Line, tblEvents.EventCode, tblEvents.MinorStop, tblEvents.MajorStop, tblEvents.Breakdowns, [MinorStop]+[MajorStop]+[Breakdowns]+[CIP]+[ProductChange]+[Maintenance], tblMachines.Zone
HAVING (((tblEvents.DayCode)>=2224 And (tblEvents.DayCode)<=2358) AND ((tblEvents.EventCode) Like "c****") AND ((tblMachines.Zone)=2))
ORDER BY tblEvents.Line;


seems a little complicated to me how would i rewrite it to match what you are suggesting

 

[Mile-O]

That's rather complicated. Order that query by the downtime (descending.)

Then build a query based on that which uses the SQL above, selecting the fields you need.

 

[Chimp8471]

ok thanks i got that now.............

to take this one step further.....

is it possible for me to do the following:

each line is in a specific Zone, eg

lines A & B are in zone 1
Lines C & D are in Zone 2 and so on

can i run the same type of query but pick the top 10 from each zone, so i will effectivly have 20 in my list 10 from zone 1 and 10 from zone 2

 

[Mile-O]

You could do two queries that select he top 10 from each of your two groups and then use a UNION query to join them.

 

[Chimp8471]

now there will be a first.........never used a union query before, but will have a look..........do they work in the same manner as a normal query ???

 

[Mile-O]

Make your two queries that select the different lines.

You should have two queries now.

Copy the SQL of one and open the other. Switch to SQL view.

Remove the semi-colon and add the word UNION.
Paste the copied SQL.

You've made a UNION query.

In order for this to work the two recordsets being unioned need to return the exact same number of fields. These fields must be of a similar datatype in both tables.

 

[Chimp8471]

i really appreciate your help, will have a look and see what mess i come up with.....will post again if(When) i have any problems

Thanks

Again

Andy

 

[Chimp8471]

i knew i would be back....

here is the sql of

query 1 - called top 10_Zone1 and
query 2 - called top 10_Zone2

top 10_Zone1

SELECT TOP 10 copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
FROM copy_eventcodespecific INNER JOIN tblMachines ON copy_eventcodespecific.Line = tblMachines.Line
GROUP BY copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
HAVING (((tblMachines.Zone)=1))
ORDER BY copy_eventcodespecific.MajorStop DESC , tblMachines.Zone;

and

top 10_Zone2

SELECT TOP 10 copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
FROM copy_eventcodespecific INNER JOIN tblMachines ON copy_eventcodespecific.Line = tblMachines.Line
GROUP BY copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
HAVING (((tblMachines.Zone)=2))
ORDER BY copy_eventcodespecific.MajorStop DESC , tblMachines.Zone;

i then joined them together to get the following:

SELECT TOP 10 copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
FROM copy_eventcodespecific INNER JOIN tblMachines ON copy_eventcodespecific.Line = tblMachines.Line
GROUP BY copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
HAVING (((tblMachines.Zone)=1))
ORDER BY copy_eventcodespecific.MajorStop DESC , tblMachines.Zone UNION SELECT TOP 10 copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
FROM copy_eventcodespecific INNER JOIN tblMachines ON copy_eventcodespecific.Line = tblMachines.Line
GROUP BY copy_eventcodespecific.MajorStop, copy_eventcodespecific.Line, copy_eventcodespecific.EventCode, copy_eventcodespecific.DayCode, tblMachines.Zone
HAVING (((tblMachines.Zone)=2))
ORDER BY copy_eventcodespecific.MajorStop DESC , tblMachines.Zone

but then i get an error message saying that one of the sql statements could refere to more thatn one table