Question abt delete qry!

  • Thread starter Thread starter Earl.AKA J.Alladien in access forum!!
  • Start date Start date
E

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!
 
hi Earl,

I have 2 tables .DEL4(4 records) and Order Details( 10 records)
Click to expand...
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!
Click to expand...
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 <--
 
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"





Stefan Hoffmann said:
hi Earl,

I have 2 tables .DEL4(4 records) and Order Details( 10 records)
Click to expand...
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!
Click to expand...
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 <--
.
Click to expand...
 
thanks Ken,

Works like a charm!

KenSheridan via AccessMonster.com said:
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

Earl.AKA J.Alladien in access forum!! said:
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!
Click to expand...
Click to expand...
 
Back
Top