Need query to show 6 largest items 2

[tarena]

I'm using Access 97 and I have a query with 2 fields "numPartRecNo" which is set to "group" in the Total field & "intSerialNo" which is set to "count" in the Total field. I want to set criteria under the "intSerialNo" field to display the 6 largest items in the field when I run the query.

How can I do this?

Thank you
Tarena

 

[BigRed1212]

Try something like:

Select numPartRecNo, top 6 count(intSerialNo)
from the_table_where_they_are
group by numPartRecNo
order by 2 desc

and see what it does.

 

[tarena]

I am obviously doing this wrong. It is not working. I have the query to count the number of serial numbers per part number and I only want to see the 6 part numbers with the largest number of serial numbers. Can I do this in the same query or do I need to create another query to do this?

Thank you
Tarena

 

[Golom]

BigRed1212's code needs a small tweak

Select TOP 6 numPartRecNo, count(intSerialNo) As [SerialCount]
from the_table_where_they_are
group by numPartRecNo
order by 2 desc

 

[BigRed1212]

Thanks Golom. I see now I put that in the wrong place. Still learning obviously.