How to use Delete Query for specific for rows > 5

[Steph_canoe]

I would to use a Delete Query for all rows of Table A after Row #5.

Is there a way I can filter so the delete query remove Rows > 5 in Table A ?

Thank you

 

[KenSheridan via AccessMonster.com]

The concept of a row number is completely foreign to a table in a relational
database. Tables are sets and as such have no intrinsic order. You'll need
to specify a sort order which determines which are the top 5 rows you want to
retain. Assuming you can do this on a column in the table then you'd use a
subquery to return the top 5 rows and restrict the rows deleted to those not
returned by the subquery, e.g.

DELETE *
FROM [Table A]
WHERE [YourField] NOT IN
(SELECT TOP 5
[YourField]
FROM [Table A]
ORDER BY [YourField]);

Ken Sheridan
Stafford, England