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

Difficulty trying to delete records

P: n/a
I have two tables: Deliveries and Invoices. An Invoice can relate to a
number of Deliveries.

The relevant fields are:
Invoices:
InvoiceID
InvoiceDate

Deliveries:
DeliveryID
DeliveryDate
InvoiceID

Sometimes deliveries are cancelled and so the associated invoice may
need to be deleted. I tried to run the following query:

Delete * From Invoices Left Join Deliveries On Invoices.InvoiceID =
Deliveries.InvoiceID Where Deliveries.InvoiceID Is Null

i.e. attempting to remove all Invoice records which do not have any
associated Delivery records.

However I get the error message:
"Could not delete from specified tables"

What is going wrong?
How can a I achieve this?

Ron

PS if I make the query into a Select query, it displays just the
records I wish to delete!

Nov 26 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"Jim Devenish" <in***************@foobox.comwrote in
news:11**********************@j44g2000cwa.googlegr oups.com:
I have two tables: Deliveries and Invoices. An Invoice can
relate to a number of Deliveries.

The relevant fields are:
Invoices:
InvoiceID
InvoiceDate

Deliveries:
DeliveryID
DeliveryDate
InvoiceID

Sometimes deliveries are cancelled and so the associated
invoice may need to be deleted. I tried to run the following
query:

Delete * From Invoices Left Join Deliveries On
Invoices.InvoiceID = Deliveries.InvoiceID Where
Deliveries.InvoiceID Is Null

i.e. attempting to remove all Invoice records which do not
have any associated Delivery records.

However I get the error message:
"Could not delete from specified tables"

What is going wrong?
How can a I achieve this?

Ron

PS if I make the query into a Select query, it displays just
the records I wish to delete!
Access often refuses to run deletes on left-joined tables.
Try this alternative method of filtering, it usually works

Delete * From Invoices
WHERE InvoiceID NOT IN
(SELECT invoiceID from Deliveries)
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Nov 26 '06 #2

P: n/a
Thanks Bob. It worked a treat

Jim
Bob Quintal wrote:
"Jim Devenish" <in***************@foobox.comwrote in
news:11**********************@j44g2000cwa.googlegr oups.com:
I have two tables: Deliveries and Invoices. An Invoice can
relate to a number of Deliveries.

The relevant fields are:
Invoices:
InvoiceID
InvoiceDate

Deliveries:
DeliveryID
DeliveryDate
InvoiceID

Sometimes deliveries are cancelled and so the associated
invoice may need to be deleted. I tried to run the following
query:

Delete * From Invoices Left Join Deliveries On
Invoices.InvoiceID = Deliveries.InvoiceID Where
Deliveries.InvoiceID Is Null

i.e. attempting to remove all Invoice records which do not
have any associated Delivery records.

However I get the error message:
"Could not delete from specified tables"

What is going wrong?
How can a I achieve this?

Ron

PS if I make the query into a Select query, it displays just
the records I wish to delete!
Access often refuses to run deletes on left-joined tables.
Try this alternative method of filtering, it usually works

Delete * From Invoices
WHERE InvoiceID NOT IN
(SELECT invoiceID from Deliveries)
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com
Nov 26 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.