Converting from Access to MSDE 2000
-
I am using a windows 2000 operating system (not by choice). A VB6 program uses an access database. I am converting access db to MSDE 2000 db. I need to write a query that Deletes all of the records in a table called ResultNotes. Below is a brief schema of the db with relationships. tblResultNotes - this is the table I want to delete from ResultID - Links to ResultID in teh tblResults table Notes tblResults startTime JobID - Links to JobID in tblJobs ResultID tblJobs CreateDate JobID I need to delete all records from tblResultNotes that have a ResultID corresponding to the tblResults table. The records that have the ResultID in the tblResults need to correspond to the tblJobs JobID. The actual criteria for deletion is that startTime and CreateDate are older than 12/1/2000. Any help is appreciated!
-
I am using a windows 2000 operating system (not by choice). A VB6 program uses an access database. I am converting access db to MSDE 2000 db. I need to write a query that Deletes all of the records in a table called ResultNotes. Below is a brief schema of the db with relationships. tblResultNotes - this is the table I want to delete from ResultID - Links to ResultID in teh tblResults table Notes tblResults startTime JobID - Links to JobID in tblJobs ResultID tblJobs CreateDate JobID I need to delete all records from tblResultNotes that have a ResultID corresponding to the tblResults table. The records that have the ResultID in the tblResults need to correspond to the tblJobs JobID. The actual criteria for deletion is that startTime and CreateDate are older than 12/1/2000. Any help is appreciated!
Since you did not include what you have tried. Try using an inner join
BEGIN TRAN
DELETE tblResultNotes FROM tblResultNotes
INNER JOIN tblResults
ON tblResultNotes.ResultID = tblResults.ResultID
ROLLBACK TRANLook up BEGIN TRAN to see what to replace ROLLBACK TRAN with to commit the delete.