By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,985 Members | 1,861 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,985 IT Pros & Developers. It's quick & easy.

Delete query problem...

P: n/a
I want to delete records from the "one" table of a one to may relationship.
There are no actual Access relationships set up. The "one" table has a
single field PK and the "many" table has a two field PK. This code works
when executed from a module (both DAO and ADO work), but not from the query
window.

DELETE a1.*, a2.Field1
FROM a1 LEFT JOIN a2 ON a1.Field1 = a2.Field1
WHERE (((a2.Field1) Is Null));

Why doesn't it work from the query window and how do I get ti to work?

Thanks.

Matthew Wells
MW****@NumberCruncher.com
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
"Matthew Wells" <MW****@NumberCruncher.com> wrote in message
news:k6*****************@bignews3.bellsouth.net...
I want to delete records from the "one" table of a one to may relationship. There are no actual Access relationships set up. The "one" table has a
single field PK and the "many" table has a two field PK. This code works
when executed from a module (both DAO and ADO work), but not from the query window.

DELETE a1.*, a2.Field1
FROM a1 LEFT JOIN a2 ON a1.Field1 = a2.Field1
WHERE (((a2.Field1) Is Null));

Why doesn't it work from the query window and how do I get ti to work?

Thanks.

Matthew Wells
MW****@NumberCruncher.com

You could use a subquery - it makes it very clear from whichtable records
are being deleted from:

DELETE FROM a1 WHERE a1.Field1
NOT IN (SELECT a2.Field1 FROM a2)
Fletcher
Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.