Question abt delete qry!

[Earl.AKA J.Alladien in access forum!!]

Hello All,

I have 2 tables .DEL4(4 records) and Order Details( 10 records)

I want to delete those records from Order Details where they match with the
records from DEL4 on 2 fields which are "OrderID" and "ProductID"'I tried a
Delete Query but that did not work!
PS the fields "OrderID" and "ProductID" exist in both tables ofcourse!
Thanks in advance for the help!

 

[Stefan Hoffmann]

hi Earl,

I have 2 tables .DEL4(4 records) and Order Details( 10 records)

What kind of weird name is [.DEL4]?

I want to delete those records from Order Details where they match with the
records from DEL4 on 2 fields which are "OrderID" and "ProductID"'I tried a
Delete Query but that did not work!
PS the fields "OrderID" and "ProductID" exist in both tables ofcourse!

Huh? This makes it very simple at all:

What have you tried exactly?

DELETE FROM [Order Details]
WHERE [OrderID] = yourOrderID AND [ProductID] = yourProductID


mfG
--> stefan <--

 

[Earl.AKA J.Alladien in access forum!!]

Hi Stefan,

I got the following:

DELETE [Order Details].OrderID, [Order Details].ProductID, *
FROM [Order Details] INNER JOIN DEL4 ON ([Order Details].ProductID =
DEL4.A_ProductID) AND ([Order Details].OrderID = DEL4.OrderID)
WHERE ((([Order Details].OrderID)=[DEL4]![OrderID]) AND (([Order
Details].ProductID)=[DEL4]![A_ProductID]));


One small note: the field in DEL4 that matches the "ProductID" field in
Order details is called "A_ProductID"

When I run it I get " Specify the table containing the records you want to
delete"

hi Earl,

I have 2 tables .DEL4(4 records) and Order Details( 10 records)

What kind of weird name is [.DEL4]?

I want to delete those records from Order Details where they match with the
records from DEL4 on 2 fields which are "OrderID" and "ProductID"'I tried a
Delete Query but that did not work!
PS the fields "OrderID" and "ProductID" exist in both tables ofcourse!

Huh? This makes it very simple at all:

What have you tried exactly?

DELETE FROM [Order Details]
WHERE [OrderID] = yourOrderID AND [ProductID] = yourProductID


mfG
--> stefan <--
.

 

[Earl.AKA J.Alladien in access forum!!]

thanks Ken,

Works like a charm!

This should do it:

DELETE *
FROM [Order Details]
WHERE EXISTS
(SELECT *
FROM DEL4
WHERE DEL4.OrderID = [Order Details].OrderID
AND DEL4.ProductID = [Order Details].ProductID);

For each row in the query the EXISTS predicate will only be TRUE where the
subquery returns at least one row, i.e. where there are matches in the
OrderID and ProductID columns. Consequently only those rows will be deleted
from Order Details.

Ken Sheridan
Stafford, England

Hello All,

I have 2 tables .DEL4(4 records) and Order Details( 10 records)

I want to delete those records from Order Details where they match with the
records from DEL4 on 2 fields which are "OrderID" and "ProductID"'I tried a
Delete Query but that did not work!
PS the fields "OrderID" and "ProductID" exist in both tables ofcourse!
Thanks in advance for the help!