We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Access Delete query - only delete the first matching record

sdholden28
sdholden28 asked
on
Medium Priority
428 Views
Last Modified: 2012-05-06
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.
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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)

Author

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".

Author

Commented:
How do I fix this error? Is there a better way to do this?
CERTIFIED EXPERT

Commented:
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;);

Author

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.
CERTIFIED EXPERT
Commented:
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);

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.