Question regarding counts in a query

  • Thread starter Thread starter JD
  • Start date Start date
J

JD

Hi..

I need to be able to write a query that can count up to 10 instances
of a certain field, even if there are more than 10 instances.
Let's say I have many different ID numbers, and some have 3 instances
(records), some have 10 and some have 20. The query needs to give me
the first 10 (well, no more than 10 records) for each ID number.
Can this be done in a query?
If it sounds confusing, please let me know and I'll try to better
explain what I'm trying to get.

Thanks much!
 
Dear JD:

In general, you can write a query that produces the TOP 10 for any
single ID. You can use this as a subquery to limit which rows show
up. To do this, you must select an ordering for the "instances"
within any given ID that determines how the TOP 10 is to be applied.
If this ordering is not unique, then you may occasionally get more
than 10 rows in the results, due to a tie in the last place.

For a specific implementation, please post a query that gives all the
rows, not just 10, and specify the column(s) by which the TOP 10 is
sorted for selection, along with specifying the column(s) that make up
the outer set for each of which you want up to 10 results. I'll try
to take it from there.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
Back
Top