delete non matching records access 2003

[d Torst]

I created a query selecting non matching records and made it a delete query & get an error "specify the table conataing the records you want to delete". Help.

Thanks

DELETE Fuel_Assistance.[Account#], Incode_US_Alpha.[Account#]
FROM Fuel_Assistance LEFT JOIN Incode_US_Alpha ON Fuel_Assistance.[Account#] = Incode_US_Alpha.[Account#]
WHERE (((Incode_US_Alpha.[Account#]) Is Null));

[patjones]

You don't need to put anything in the DELETE clause:

DELETE FROM Fuel_Assistance LEFT JOIN Incode_US_Alpha ON   
            Fuel_Assistance.[Account#] = Incode_US_Alpha.[Account#]
WHERE (((Incode_US_Alpha.[Account#]) Is Null));

BUT, this may not work. DELETE and UPDATE operations on JOINs are subject to referential integrity constraints. If the tables are designed in such a way that a DELETE operation from one side of the JOIN does not cascade to the other side of the JOIN, then the operation will fail.

You could also DELETE from the two tables separately.

Pat

[d Torst]

Thanks for you time and advice. I chose to write a small VB function instead.

Thanks again.

[patjones]

OK. If you're able to post your solution to the thread, please do so as people might like to know how you went about this. Thanks.

[d Torst]

This is the solution I came up with

Public Function Reorg_File()
'Select Inactive customers to delete
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strCriteria As String

Set db = CurrentDb
DoCmd.SetWarnings False
' Created a make table query to build tbl_Assist_Delete records
DoCmd.OpenQuery "qry_Assist_Delete_Table", , dbOpenDynaset
Set rs1 = db.OpenRecordset("tbl_Assist_Delete", dbOpenDynaset)
Set rs2 = db.OpenRecordset("tbl_Assistance", dbOpenDynaset)

  With rs1
     rs1.MoveFirst
      While Not rs1.EOF
      
     rs2.MoveFirst
     strCriteria = "[Account#] = " & """" & rs1.Fields("Account#") & """"
    
       rs2.FindFirst strCriteria
          
      If rs2.NoMatch Then
        MsgBox "Record not found"
      End If
          
            rs2.Delete
            
            .MoveNext
     Wend
        
   End With
 
rs1.Close
rs2.Close
End Function