Get rid of ties in Access Query

[Grd]

Hi there,

I'm using a Top 5 query and I need to limit it to just 5. I'm getting
matches so it ends up being more than 5.

There doesn't seem to be an option to limit ties. Is this possible in Access?

Any help would be greatly appreciated

Tx

Suzanne

 

[KARL DEWEY]

Try using a 'Totals' query.

 

[Michel Walsh]

Add the primary key at the end of the order by clause. That will
definitively break any tie:

ORDER BY field1, field2, PrimaryKeyField



Vanderghast, Access MVP

 

[Grd]

tHATS IT THANKS

Add the primary key at the end of the order by clause. That will
definitively break any tie:

ORDER BY field1, field2, PrimaryKeyField



Vanderghast, Access MVP

 

[John Spencer (MVP)]

If you have primary keys, add them to the sort

SELECT TOP 5 FieldA
FROM TableA INNER JOIN TableB
ON TableA.FieldC = TableB.FieldX
ORDER BY FieldA, TableA.PrimaryKey, TableB.PrimaryKey

By definition that must return only five records.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County