asked on

Access Delete query - only delete the first matching record

I'm trying to create a delete query to delete certain records. A select query that returns the record I want can be created very simply with:

SELECT TOP 1 [Batches-Brookside].Invoice2
FROM [Batches-Brookside]
WHERE ((([Batches-Brookside].Invoice2)=[Forms]![AGA_Input-Brookside]![Invoice#]));

I can't however convert this straight to a delete query. It drops the "TOP 1" and says syntax error if I add it back in. How do I accomplish this?

For some background on what's going on and why... The user can enter Batch#'s and amounts via a subform contained on an "Invoice" main form. I'm trying to give the user the ability to delete a record they may have mistyped or something, without trashing the entire record, forcing them to start over completely. The DeleteQuery I currently have deletes all Batch entries pertaining the the current invoice, which is not the best solution if you have say 10 or more batches. It is better than having to reenter the entire invoice, but a "one-record delete" option combined with an "all-record-delete" option would be ideal, hence the question.

peter57r

If your select query shows you the correct record to delete then I'm afraid it is by chance rather than by design.
A Top N query requires an Order By clause to ensure that the records are sorted into the correct sequence to get the 'Top 1 ' to the top of the list.

But assuming you can sort that out(!) then you can do...

Delete * from [Batches-Brookside]
where [Batches-Brookside].Invoice2 IN
(SELECT TOP 1 [Batches-Brookside].Invoice2
FROM [Batches-Brookside]
WHERE [Batches-Brookside].Invoice2=[Forms]![AGA_Input-Brookside]![Invoice#]
Order By somefieldtogettherequiredrecordintofirstpostion)

sdholden28

ASKER

Alright I added an ID autonumber field to the table Batches-Brookside and have it sorted largest to smalles which means last record input will always be on top. My query now:

DELETE *
FROM [Batches-Brookside]
WHERE [Batches-Brookside].Invoice2 IN
(SELECT TOP 1 [Batches-Brookside].Invoice2, [Batches-Brookside].[Batch#], [Batches-Brookside].BatchAmount

FROM [Batches-Brookside]
WHERE ((([Batches-Brookside].Invoice2)=[Forms]![AGA_Input-Brookside]![Invoice#]))
ORDER BY [Batches-Brookside].ID;);

This produces the error "You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field".

sdholden28

ASKER

How do I fix this error? Is there a better way to do this?

peter57r

DELETE *
FROM [Batches-Brookside]
WHERE [Batches-Brookside].Invoice2 IN
(SELECT TOP 1 [Batches-Brookside].Invoice2
FROM [Batches-Brookside]
WHERE ((([Batches-Brookside].Invoice2)=[Forms]![AGA_Input-Brookside]![Invoice#]))
ORDER BY [Batches-Brookside].ID;);

sdholden28

ASKER

That solution runs and does not produce an error message. However, it still deletes all rows that much the Invoice2 criteria, instead of just the first record that matches. The goal is only to delete the first record.

ASKER CERTIFIED SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial