• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

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.
0
sdholden28
Asked:
sdholden28
  • 3
  • 3
1 Solution
 
peter57rCommented:
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)
0
 
sdholden28Author Commented:
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".

0
 
sdholden28Author Commented:
How do I fix this error? Is there a better way to do this?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
peter57rCommented:
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;);
0
 
sdholden28Author Commented:
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.
0
 
peter57rCommented:
i didn't realise that Invoice2 was not the identifying value.

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

0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now