Difficulty trying to delete records

[Jim Devenish]

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!

[Bob Quintal]

"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

[Jim Devenish]

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