Many to Many Delete Query

[ICCIIT]

I have two tables
tblDuplicateItems (list of many unique policy numbers)
tblPolicyData (many items with same policy no)

I have tried to create a delete query to remove all the items from tblPolicydata that have related policy numbers in tblDuplicateItems

It seems that Delete queries can only work on a single criteria and will not loop through the table tblDuplicateItems

Is it possible to create a many to many delete query based on this scenario?

 

[ICCIIT]

Resolved this by creating a function to loop through the recordset of the tblDuplicateItems and delete them:

Public Function RemoveDuplicateItems()

Dim rs As Recordset
Dim db As Database

Set db = CurrentDb
Set rs = db.OpenRecordset("tblDuplicateItems")

DoCmd.SetWarnings False
With rs

rs.MoveFirst
Do Until rs.EOF

DoCmd.RunSQL "DELETE * from tblPropertyItems WHERE [Policyno]= '" & ![Policyno] & "' ;"
rs.MoveNext
Loop

End With

DoCmd.SetWarnings True

End Function

Amazing what you can do for yourself when needs arise!!

 

[PHV]

SQL code:

DELETE P.*
FROM tblPropertyItems P INNER JOIN tblDuplicateItems D ON P.Policyno=D.Policyno

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ICCIIT]

PHV
Thanks for your reply, I cannot get to try your suggestion out right now.

Is your method using alias tables P and D for respective tables? rather than creating the function?

 

[Andrzejek]

It may also work:

DELETE FROM tblPolicyData
WHERE tblPolicyData.PolicyNumber IN
([blue]SELECT DISTINCT PolicyNumber
FROM tblDuplicateItems[/blue])

Have fun.

---- Andy