I am looking to create a query that only displays to me the top 10 (or less) records. I am of course using the Select Top 10 method

When I say or less, I mean that currently my report is showing 14 records. However 5 of the values in the result set are the value of '2'.

Is there anyway that I can set it so that if a group of values are the same and the result set will then exceed 10, then the report wil exclude them and lets say just show the Top 8?

I have looked through the search options on here and not found the answer, although there were a good number of hits, none of which appeared to be relevant.

Regards

Ice Rhino (what's the derivation of that name?),

Very roughly (using pseudo-code), here's one possible (brute force) approach:

Run an append query (getting the top ten), throwing all recs into a temp table.

INSERT INTO TempTABLE
SELECT TOP TEN * FROM TblValues


Open a recordset connected to the temp table.

rst.open "SELECT * FROM TempTable ORDER BY RelevantField"


Count the recs.

MyCountVariable = rst.recordcount


If there are more than ten recs, move to the last rec.

IF MYCountVariable > 10 then
rst.movelast


Memorize the relevant value:

MyRelevantVariable = rst.fields("RelevantFieldName")


Delete the record

rst.delete


Loop backwards through the rest of the recordset and
delete recs matching the value of that first deleted rec.

rst.movefirst
rst.movelast
FOR i = (MyCountVariable - 1) to 0 step -1
If rst.fields("RelevantFieldName") = MyRelevantVariable Then
rst.Delete
rst.movefirst
rst.movelast
Else
Exit FOR
End if
Next i


When finished, run another query, selecting all recs from your temp table.

One catch: If you have more than 10 records returned by your top ten query and ea. rec's "relevant field" has the same value, all recs will be deleted.

Regards,
Tim

I am trying to digest your response now. Thank you for the suggestions.

In answer to your question on how Ice Rhino arrived. It was because I was playing one of my favourite games, Battlezone II. One of the levels involved the escorting of a key vehicular unit within the game. The escort was around a Snow covered Planet. You had to prevent the unit going onto the 'Ice'. One of the indigineous creatures on the planet was a two legged 'Rhino'variant. Therefore I thought, 'Ahhh, Ice Rhino'

And there you have my thought process.

Regards

Alternatively, you use a series of two queries.

For instance, if the following query returns 11 records because there is a tie in Amount in the 9th to 11th records.

QueryOne:-
SELECT TOP 10 *
FROM tblSales
ORDER BY [Amount] DESC;

This second query will exclude the 9th to 11th records and return only 8.

QueryTwo:-
SELECT *
FROM QueryOne
WHERE IIf(DCount("*","QueryOne")>10, [Amount]<>DMin("Amount", "QueryOne"), True);

You can base your report on the second query.

Ahh that sounds a bit easier

Thanks