loop delete records 2

[macca007]

Hi i would like to if its possible to check 2 tables and if both hospital number matches do nothing otherwise if they don't match delete record from table1.

the 2 table are tblPatient and tblTreatments. I would like to delete records from tblpatients if they don't match to tbl treatments. The look fields are hospitalNo

cheers

 

[flyover789]

the following function should work for you:

Function delete_not_matching()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
strsql = "select t1.hospitalNo " & _
"from tblPatient t1 LEFT JOIN tblTreatments t2 ON t1.hospitalNo=t2.hospitalNo " & _
"where t2.hospitalNo is null;"
Dim i As Integer
Set db = Application.CurrentDb
Set rs = db.OpenRecordset(strsql)
i = rs.RecordCount
Set rs1 = db.OpenRecordset("tblPatient")
DoCmd.SetWarnings False
Do Until i = 0

[blue]'now deleting[/blue]
[blue]'version1: if your hospitalNo is of type string, use:[/blue]
DoCmd.RunSQL "delete * from tblPatient t where t.hospitalNo = '" & rs.Fields(0).Value & "';"

[blue]'version2: if your hospitalNo is numeric, use:[/blue]
DoCmd.RunSQL "delete * from tblPatient t where t.hospitalNo = " & rs.Fields(0).Value & ";"
[blue]'end of deleting[/blue]

rs.Close
i = i - 1
Set rs = db.OpenRecordset(strsql)
Loop
DoCmd.SetWarnings True
MsgBox "done"
End Function

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[flyover789]

and again a correction:
the previously posted solution only deletes one record.
following is the corrected one:

Function delete_not_matching()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strsql As String
strsql = "select t1.hospitalNo " & _
"from tblPatient t1 LEFT JOIN tblTreatments t2 ON t1.hospitalNo=t2.hospitalNo " & _
"where t2.hospitalNo is null;"
Set db = Application.CurrentDb
Set rs = db.OpenRecordset(strsql)
Set rs1 = db.OpenRecordset("tblPatient")
DoCmd.SetWarnings False

Do While rs.RecordCount > 0

[blue]'now deleting
'version1: if your hospitalNo is of type string, use:[/blue]
DoCmd.RunSQL "delete * from tblPatient t where t.hospitalNo = '" & rs.Fields(0).Value & "';"

[blue]'version2: if your hospitalNo is numeric, use:[/blue]
DoCmd.RunSQL "delete * from tblPatient t where t.hospitalNo = " & rs.Fields(0).Value & ";"
[blue]'end of deleting[/blue]

rs.Close
Set rs = db.OpenRecordset(strsql)
Loop

rs.close
set rs = nothing
DoCmd.SetWarnings True
MsgBox "done"
End Function

and I agree, depending on the size of your tables, the function takes tiiiime ... well, it's just a suggestion

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[MichaelRed]

While "looping" through recordsets for matches / differences cretainly can and is often done, you should think about the concepts of relational databases. They are designed for "Set" operations, and are generally quite a bit more efficient (faster) in this mode than in the individual record serch and operate mode.

A subquery of the not matched variety as the criteria for a straight delete query would match the 'strength' of the database processing to the problem as I would interpert it.




MichaelRed
mlred@verizon.net

 

[PHV]

Just follow the query wizard (unmatched query).

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[flyover789]

I've also first tried the pure SQL approach like:

DELETE t1.* FROM tblPatient t1 LEFT JOIN tblTreatments t2 ON t1.hospitalNo=t2.hospitalNo WHERE t2.hospitalNo is null;

But this (or similar constructs without using the join ...) didn't work because the table to be deleted from is involved in the where clause ...

Greetings,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[PHV]

And what about this ?
DELETE FROM tblPatient WHERE hospitalNo NOT IN (SELECT hospitalNo FROM tblTreatments);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[flyover789]

ok PHV, I've also made this approach yesterday, in my testcase using tables with about 20000 records. and it didn't work (Access simply hung itself up ). I've retried it now with shortened tables (about 1000 recs) and it worked ...

so also here the size of the tables is a considerable factor.

Greetings,
fly

Martin Serra Jr.

http://www.mserrasystems.com

 

[oharab]

20,000 records isn't a huge amount, I've got tables with over 100,000 records!
I would try compacting your database (don't forget to backup!) and then making sure you have indexes on the hospitalNo field in both tables.

That should speed things up.

Ben

----------------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

"Where are all the stupid people from...
...And how'd they get so dumb?"

NoFX-The Decline
----------------------------------------------