Learn how to a build a cloud-first strategyRegister Now


ms access delete query with attachment

Posted on 2012-08-12
Medium Priority
Last Modified: 2013-03-27
I have a table that has a field that has attachements.  I tried to run a delete query to remove some records but got a message:

"An Update or Delete query cannot contain a multi-valued field"

Any suggestions on how to delete these records?

Question by:johnmadigan
  • 2
LVL 13

Expert Comment

ID: 38285449
Can you post your total query? Can you also post the result of the subquery? You can try to use LIMIT 1 in the subquery. Then he'll give 1 result.

But we can do a check-up if you provide us the query, etc..

Author Comment

ID: 38285455
Here is the sql for the delete query:

DELETE [Purchase Requests].Date_of_Request, [Purchase Requests].Status, [Purchase Requests].ID, [Purchase Requests].Date_Needed, [Purchase Requests].Remarks_Instructions, [Purchase Requests].Preferred_Supplier, [Purchase Requests].Cost_Center, [Purchase Requests].Job_Number, [Purchase Requests].Ship_To, [Purchase Requests].Quote_or_Invoice_Number, [Purchase Requests].HMPCO_Attn_To, [Purchase Requests].Ship_Method, [Purchase Requests].Order_Date, [Purchase Requests].Arrival_Date, [Purchase Requests].Inv_Cont_Purch_Remarks, [Purchase Requests].PO_Number, [Purchase Requests].Requestor, [Purchase Requests].Approved_By, [Purchase Requests].User_Names, [Purchase Requests].Date_Approved, [Purchase Requests].Lock, [Purchase Requests].Attachment, [Purchase Requests].[5S+1], [Purchase Requests].JobRel
FROM [Purchase Requests]
WHERE ((([Purchase Requests].Date_of_Request)<#6/30/2012#) AND (([Purchase Requests].Status)="CLOSED"));

the problem is that the Attachement field can have multiple attachements so when I try a simple delete query it does not like it.

Not sure how I can delete the records.
LVL 13

Accepted Solution

Xaelian earned 750 total points
ID: 38285466
You can create a loop. That deletes the rows  in the attachement table. Afterwards you can execute your query.

Assisted Solution

joaoalmeida earned 750 total points
ID: 38294789
If you want to delete the records, don't specify the fields to delete.


FROM [Purchase Requests]
WHERE ((([Purchase Requests].Date_of_Request)<#6/30/2012#) AND (([Purchase Requests].Status)="CLOSED"));

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question