MS Access Query – How to Count and Delete Rows Greater Than N

ms access

Can someone help me with a SQL (Access) query to delete all rows greater than n entries ordered by ID?

I.e. say I have 1200 rows of data with ID incremented . I need delete the first amount leaving only 200.

If I have only 199 rows of data, then I need to preserve them all.

Best Answer

I don't have access to a working copy of MS Access to test this but what you might be looking for is this:

delete * from table_name where ID not in(
select top 200 ID from table_name order by id desc
);

Related Solutions

Ms-access – How to conditionally summarize multiple rows in Microsoft Access

I'd also like to see the ERD (Entity Relationship Diagram) to get a better understanding of your problem.

I'm thinking if you're creating a report to present the data in this fashion, you can setup a grouping based on an expression, either using built-in functions or use a user-defined function in a VBA module.

Ms-access – Removing duplicates from a query with conditions in Access 2007

If all the "duplicate" rows differ only at the InterestRate and you never have more than one row with rate over 0, and (one more assumption) there is no negative interest rate, then you can simply:

SELECT 
    TransactionID, MAX(InterestRate) AS InterestRate,
    col1, col2, ..., colN               -- all the other columns

FROM
    tableX AS t
GROUP BY
    TransactionID,
    col1, col2, ..., colN ;             -- all the other columns

Best Answer

Related Solutions

Ms-access – How to conditionally summarize multiple rows in Microsoft Access

Ms-access – Removing duplicates from a query with conditions in Access 2007

Related Question