Could Someone Please Help With This ???? Returning Top Values

[NewComer]

I am trying to create a query that returns only the top 6 part numbers per task number(ascending) and it associated quantity(desc).

The only fields included in this query are the 3 stated above plus 1 more listed as follows:

Task
Part_No
Quantity
Nomenclature

If anyone know how accomplish this, I could certainly use the help.

Thanks

 

[GComyn]

In the Query builder, if you look at the menus at the top... you will see the word 'All'. If you change this to the number you want (6), that is all you'll get.

here is an sql statement as an example:

Select Top 6 Table.Field, Table.Field1, Table.Field2 from TABLE Order BY Table.Field

just put the words 'Top 6' after the word select in the sql, and there ya go!

GComyn

 

[tigi1]

That does not solve Newcomers problem, he requires top6 for each of the different task nos. This cannot be done in sql.
I remember putting the same issue in one of the forums. I had to finally solve it by putting the whole table through a form, using a counter that is reset at every change of group. After these nos are assigned, I pick up all records within required value (<= 6) in counter field. That gives the listing.
Perhaps we should ask MS people to put up a sql command for GROUP TOP values in addition to the TOP values at present.
Tigi1

 

[tigi1]

Hi
That does not solve Newcomers problem, he requires top6 for each of the different task nos. This cannot be done in sql.
I remember putting the same issue in one of the forums. I had to finally solve it by putting the whole table through a form, using a counter that is reset at every change of group. After these nos are assigned, I pick up all records within required value (<= 6) in counter field. That gives the listing.
Hope this is helpful
Perhaps we should ask MS people to put up a sql command for GROUP TOP values in addition to the TOP values at present.
Tigi1

 

[NewComer]

Thank you both with your help. Tigi1, your proposal seems to be what I am looking for. Could you possibly give me more direction on how to increment and reset counter on form. Have never done this before.
Thanks

 

[raskew]

It's certainly doable with SQL. Take a look at this thread to see a couple of different approaches:

http://bbs.elementkjournals.com/bbs...umber=58509&page=&view=&sb=&o=&vc=1#Post58509

 

[BoxHead]

Take a look at tlbroadbent's post in the following thread.

TOP 10 Per region
thread701-171184


Probably something along the lines of...

Select Task, Part_No, Quantity, Nomenclature
From YourTable As Temp
Where Part_No In 
(Select Top 6 Part_No
 From YourTable Where Task = Temp.Task
 Order By Quantity Desc)

HTH
John

Use what you have,
Learn what you can,
Create what you need.