Find Unmatched Query

  • Thread starter Thread starter Dan
  • Start date Start date
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,
 
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.
Click to expand...

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).
 
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,
|
|
|
 
Back
Top