ACCESS FEED: RE: [MS_AccessPros] Help with Delete Query

Kamis, 31 Mei 2012

RE: [MS_AccessPros] Help with Delete Query

Troy-

Alternative syntax that's probably faster:

DELETE TableB.*
FROM TableB LEFT JOIN TableA
ON TableB.IDNumber = TableA.IDNumber
WHERE TableA.IDNumber IS NULL;

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/
(Paris, France)

-------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Troy Sherven
Sent: Thursday, May 31, 2012 9:30 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Help with Delete Query

John,

Thanks for the reply. Should I have any performance concerns? TableB
has about 3,500 records, and tableA has about 300 records. The query
you gave me makes sense, but every time I try to run it (as a delete
query or a select query), Access hangs up on me. Any ideas?

Thanks!

Troy

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of John
Viescas
Sent: Thursday, May 31, 2012 11:30 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Help with Delete Query

Troy-

DELETE * FROM TableB

WHERE TableB.IDNumber NOT IN

(SELECT TableA.IDNumber FROM TableA)

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%40yahoogroups.com>
[mailto:MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Troy
Sherven
Sent: Thursday, May 31, 2012 5:50 PM
To: MS_Access_Professionals@yahoogroups.com
<mailto:MS_Access_Professionals%40yahoogroups.com>
Subject: [MS_AccessPros] Help with Delete Query

I have 2 tables. Table A is in a SQL DB (Linked to Access). Table B is
in Access 2010. I have a query that inserts an ID number from table A
into table B. This is working fine, except for the fact that table B
eventually gets a lot of records. I'm trying to write a delete query
that will delete any record from table B that isn't in table A. The
tables are joined by ID number (which is also the field that I'm
inserting into table B). I'm struggling figuring out how to write a
delete query using Where tableA.IDNUMBER <> tableB.IDNUMBER. Any ideas?

Thanks,

Troy Sherven

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

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

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