Another Top 10 Query Problem

[Ice Rhino]

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

 

[pono1]

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

 

[Ice Rhino]

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

 

[EMP]

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.

 

[Ice Rhino]

Ahh that sounds a bit easier

Thanks