ACCESS FEED: RE: [MS_AccessPros] delete from an unmatched query

Selasa, 03 Juli 2012

RE: [MS_AccessPros] delete from an unmatched query

Liz-

It would work if one of the sets of key fields in the JOIN were unique. Since
one side is a query, the fields in Drawing - SolutionRev, SolutionItem, ECO, and
Drawing - must together be a unique key or the Primary key.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
<http://www.viescas.com/> http://www.viescas.com/

(Paris, France)

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Liz Ravenwood
Sent: Tuesday, July 03, 2012 5:20 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: [MS_AccessPros] delete from an unmatched query

Greetings Pros, and happy soon-to-be 4th of July.

I'm trying to take a result from an unmatched query and delete them out of a
table.

Here's my delete sql:

DELETE Drawing.*
FROM OnDrawingNotOnECBThisECR INNER JOIN Drawing ON
(OnDrawingNotOnECBThisECR.SolutionRev = Drawing.SolutionRev) AND
(OnDrawingNotOnECBThisECR.SolutionItem = Drawing.SolutionItem) AND
(OnDrawingNotOnECBThisECR.ECO = Drawing.ECO) AND
(OnDrawingNotOnECBThisECR.Revision = Drawing.Revision) AND
(OnDrawingNotOnECBThisECR.Drawing = Drawing.Drawing);

From this unmatched query:
SELECT Drawing.Drawing, Drawing.Revision, Drawing.ECO, Drawing.SolutionItem,
Drawing.SolutionRev, Drawing.Received
FROM Drawing LEFT JOIN [ECB drawing action] ON (Drawing.[ECR #] = [ECB drawing
action].[CRF #]) AND (Drawing.Drawing = [ECB drawing action].[Drawing #])
WHERE (((Drawing.[ECR #])=[Forms]![RFC Input]![txtECR]) AND (([ECB drawing
action].[Drawing #]) Is Null));

I'm getting an error message "could not delete from specified tables."

What am I doing wrong?

Liz Ravenwood
Database Developer / Programmer
Super First Class Products
B/E Aerospace
O: 1.520.239.4808
www.beaerospace.com
This email (and all attachments) is for the sole use of the intended
recipient(s) and may contain privileged and/or proprietary information. Any
unauthorized review, use, disclosure or distribution is prohibited. If you are
not the intended recipient, please contact the sender by reply e-mail and
destroy all copies of the original message.

[Non-text portions of this message have been removed]