Select 10 or fewer records based on ID field within table.

[jkpats12]

Hello,

I've looked through the thread and was unable to find exactly what I'm looking for ?

Basically I have a table with multiple fields & records and I would like to key on the ID field which has a slew of different ID's..... these ID's can be the same & show up on the table anywhere from 1 to 600 times.

What I'm trying to do is select each ID and pull only 10 records for each or if less than 10 pull in those as well.

So instead of pulling in the 600 records for that particular ID I only want to pull in 10 of those records, and for the ID that only has the 1 record pull in the info for that as well.

Any help would be greatly appreciated.

Thank you

 

[RuralGuy]

Have you tried SELECT TOP 10?

 

[jkpats12]

I have RuralGuy, but my table consists of roughly 2,700 records and it only gives me the Top 10 of this table.

Since I have multiple occurrences of the ID in the table....(there are actually 37 different ID's in the table) but each ID shows up a number of times to create the 2,700 records.

I'm trying to pull each ID and 10 or fewer records for each.

Thanks

 

[RuralGuy]

I'm not very good at queries but have you tried a GroupBy on the ID?

 

[jkpats12]

Yes I've tried pretty much everything to this point, I'll keep plugging away at it.

Thanks again for the help.

 

[The_Doc_Man]

You might do this in multiple passes. Add an integer or long integer field that you call ORDINAL or some similar name that conveys this concept to you.

Now, you have a way to sort these BESIDES the ID, I am thinking. Whether it is date or the value of some other field, there is a field you can use to help you make the selection. Basically, write an update query to update the ORDINAL field to rank-order each record only among records having the same ID. So you number the records almost like a serial number.

Suppose, for example, you wanted the top 10 records based on the 10 largest money amounts in a field called MONEY. So you would order by MONEY and update the ORDINAL field to 1 + the count of all records with the same ID number and larger MONEY values. Use DCount (which you can look up in Access help if you are unsure of it.)

Now, when this is done, just select based on the ORDINAL being <= 10 and present the results with GROUP BY the ID field.

If you have at least 10 records for a given ID, you'll get 10 records. If you have fewer for some IDs, you'll get fewer.

The ONLY monkey wrench is TIES. If you have the same MONEY value (in this scheme) for 11 records, you would get 11 records back because of the tie. So if there is more than one way to sort, consider using a second or even a third sort as a tie-breaker to prevent exceeding the 10 count.

 

[cardgunner]

What criteria will you use for the ten records?