Last updated: 24/03/2008 16:28:46 GMT

limit records in access

Well you can't, but this is a neat way of emulating it:

Example table:

columnA and columnB. The table has more than 10 records in it. We want to retrieve records 6 to 10 only.

SELECT * FROM (SELECT TOP 5 columnA, columnB FROM (SELECT TOP 10 columnA, columnB FROM table ORDER BY columnB DESC ORDER BY columnB ASC) ORDER BY columnB DESC

So we get the top 10 records sorted one way, flip them and take the top 5 then flip them back to give us what would have been records 6 to 10.

This also works in SQL server.