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

Delete records in a query by referencing another

P: n/a
I have a main table that I need to delete records that arn't referenced in
another. Query says I cannot delete.
If I remove the reference query all deletes ok.
Hope there is a way around this ?
Query as follows:
DELETE Items.*, Items.SupplierCode, Items.UpdatedDateTime, [Check if
Existing Quote Number exists before delete].ItemNumber
FROM [Check if Existing Quote Number exists before delete] RIGHT JOIN Items
ON [Check if Existing Quote Number exists before delete].ItemNumber =
Items.ItemNumber
WHERE (((Items.SupplierCode)=[Forms]![frmImport]![cmbSuppliers]) AND
((Items.UpdatedDateTime)<>[Forms]![frmImport]![DTNOW1]) AND (([Check if
Existing Quote Number exists before delete].ItemNumber) Is Null));

Are using this below query in a form and works fine except deletes all
records. I need to reference the other query so some items are not deleted.
Hope someone can help ?

DoCmd.RunSQL " DELETE Items.*, Items.SupplierCode, Items.UpdatedDateTime
FROM Suppliers INNER JOIN Items ON Suppliers.SupplierCode =
Items.SupplierCode WHERE
(((Items.SupplierCode)=[Forms]![frmImport]![cmbSuppliers]) AND
((Items.UpdatedDateTime)<>[Forms]![frmImport]![DTNOW1]));"

Many thanks
Rob
Apr 11 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Robin schreef:
I have a main table that I need to delete records that arn't
referenced in another. Query says I cannot delete.
If I remove the reference query all deletes ok.
Hope there is a way around this ?
Query as follows:
DELETE Items.*, Items.SupplierCode, Items.UpdatedDateTime, [Check if


Try DELETE DISTINCTROW Items.* FROM ....

Apr 11 '06 #2

P: n/a
Thankyou Kaniest ... for your help .. all works ok now..
Rob

"kaniest" <ka*****@invalid.invalid> wrote in message
news:44***********************@news.xs4all.nl...
Robin schreef:
I have a main table that I need to delete records that arn't
referenced in another. Query says I cannot delete.
If I remove the reference query all deletes ok.
Hope there is a way around this ?
Query as follows:
DELETE Items.*, Items.SupplierCode, Items.UpdatedDateTime, [Check if


Try DELETE DISTINCTROW Items.* FROM ....

Apr 12 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.