Find Unmatched Query


D

Dan

I have used the wizard to create an unmatched query which works great. Now
I want to delete all the records that this query finds. I tried to convert
it to a delete query, but then it has the error:
Cannot delete from specified table.
I have also tried to combine it into a delete query that I had already
created. The following is the SQL from each query:

Unmatched:
DELETE NSNDetail.*, ID.SL3NSN
FROM NSNDetail LEFT JOIN ID ON NSNDetail.NSN = ID.SL3NSN
WHERE (((ID.SL3NSN) Is Null));

Delete:
DELETE ID.*, ID.ID
FROM ID
WHERE (((ID.ID)=[Forms]!["SL3 Item Input"]![ID]));

Each query does the job by themselves, but I need to have the delete query
work for all unmatched without any more input than pushing a button once.

Thanks,
 
Ad

Advertisements

G

Guest

Dan said:
I have used the wizard to create an unmatched query which works great. Now
I want to delete all the records that this query finds. I tried to convert
it to a delete query, but then it has the error:
Cannot delete from specified table.

This arises because when you change a SELECT query to a DELETE query, it leaves both tables in the DELETE clause. Try either:

DELETE NSNDetail.*
FROM NSNDetail LEFT JOIN ID ON NSNDetail.NSN = ID.SL3NSN
WHERE (((ID.SL3NSN) Is Null));

or (because this doesn't always work, depending on the defined relationships)

DELETE NSNDetail.*
FROM NSNDetail
WHERE NOT EXISTS
(SELECT ID FROM ID WHERE ID.SL3NSN = NSNDetail.NSN);

The NOT EXISTS clause can be very slow, since the optimizer doesn't handle it very well, but can be counted on to work (eventually).
 
Ad

Advertisements

E

Eric Butts [MSFT]

Hi Dan,

Are you saying that your Delete query

DELETE NSNDetail.*, ID.SL3NSN
FROM NSNDetail LEFT JOIN ID ON NSNDetail.NSN = ID.SL3NSN
WHERE (((ID.SL3NSN) Is Null));

works and then you are needing to run this delete query

DELETE ID.*, ID.ID
FROM ID
WHERE (((ID.ID)=[Forms]!["SL3 Item Input"]![ID]));

But you would like to do it all with one click?

Have you tried calling both queries from a macro using SetWarnings to turn
off and on the warning messages?

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support
(e-mail address removed)
"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."

This posting is provided "AS IS" with no warranties, and confers no rights


--------------------
| From: "Dan" <[email protected]>
| Newsgroups: microsoft.public.access.queries
| Subject: Find Unmatched Query
| Lines: 23
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <[email protected]>
| Date: Sun, 02 May 2004 20:32:31 GMT
| NNTP-Posting-Host: 65.41.254.176
| X-Complaints-To: (e-mail address removed)
| X-Trace: newsread2.news.atl.earthlink.net 1083529951 65.41.254.176 (Sun,
02 May 2004 13:32:31 PDT)
| NNTP-Posting-Date: Sun, 02 May 2004 13:32:31 PDT
| Organization: EarthLink Inc. -- http://www.EarthLink.net
| Path:
cpmsftngxa10.phx.gbl!TK2MSFTFEED01.phx.gbl!TK2MSFTNGP08.phx.gbl!news-out.cwi
x.com!newsfeed.cwix.com!border1.nntp.ash.giganews.com!border2.nntp.ash.gigan
ews.com!nntp.giganews.com!elnk-atl-nf1!newsfeed.earthlink.net!stamper.news.a
tl.earthlink.net!newsread2.news.atl.earthlink.net.POSTED!772bdb0f!not-for-ma
il
| Xref: cpmsftngxa10.phx.gbl microsoft.public.access.queries:199327
| X-Tomcat-NG: microsoft.public.access.queries
|
| I have used the wizard to create an unmatched query which works great.
Now
| I want to delete all the records that this query finds. I tried to
convert
| it to a delete query, but then it has the error:
| Cannot delete from specified table.
| I have also tried to combine it into a delete query that I had already
| created. The following is the SQL from each query:
|
| Unmatched:
| DELETE NSNDetail.*, ID.SL3NSN
| FROM NSNDetail LEFT JOIN ID ON NSNDetail.NSN = ID.SL3NSN
| WHERE (((ID.SL3NSN) Is Null));
|
| Delete:
| DELETE ID.*, ID.ID
| FROM ID
| WHERE (((ID.ID)=[Forms]!["SL3 Item Input"]![ID]));
|
| Each query does the job by themselves, but I need to have the delete query
| work for all unmatched without any more input than pushing a button once.
|
| Thanks,
|
|
|
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top