INSERT into the table if the data does not exist

[ramindya1234]

Database information:

I have three tables Patient , Scheduling and Tracking tables

Patient table has ( MRN, Name ) Where MRN is Primary Key.
Scheduling table has ( ID, MRN, StudyID ) WHERE ID ( Autonumber) is primary key.
Tracking table has ( ID, MRN, StudyID ) WHERE ID ( Autonumber) is primary key.

The relationship between Patient table and the Scheduling table is one to many relationship.Similarly, the relationship between Patient table and the Tracking is table is one to many relationship.
Because there can be any number of health studies for a patient.

Question:
The form Scheduling shows the MRN and StudyID and when I hit save the MRN and STUDYID saves in the Scheduling table but the same data should be inserted into Tracking table if the same combination does not exist.

For example: Scheduling table has the following data:
MRN STUDYID
1234 1
1234 2

and so when I open the form it will show MRN as 1234 and StudyID as 2 or 1 depending on my previous form selection of the study ID.

Assume if initially the Tracking table has only following data:
MRN STUDYID
1234 1

and if it doesnt have
MRN STUDYID
1234 2

Then when I save the form scheduling with MRN = 1234 and Studyid = 2

The Tracking table should also automatically have the following data:
MRN STUDYID
1234 1
1234 2

ISSUE/SOLUTION ?
Basically I like to write a VBA code behind the form Scheduling where it should save the data in the table Tracking if the data ( MRN and STUDYID) does not exist.

I got the approximate syntax but it does not work

strSQL = "SELECT dbo_tblScheduling.MRN, dbo_tblScheduling.STUDYID INTO dbo_tblTracking FROM dbo_tblScheduling WHERE not exists (select dbo_tblTracking.MRN, dbo_tblTracking.STUDYID FROM dbo_tblTracking Where dbo_tblTracking.MRN = dbo_tblScheduling.MRN AND dbo_tblTracking.STUDYID = dbo_tblScheduling.STUDYID)"

or

'INSERT INTO dbo_tblTracking (MRN, STUDYID) SELECT MRN, STUDYID FROM dbo_tblTracking WHERE not exists (select MRN, STUDYID FROM dbo_tblScheduling Where dbo_tblTracking.MRN = dbo_tblScheduling.MRN AND dbo_tblTracking.STUDYID = dbo_tblScheduling.STUDYID)

Can somebody provide me help? I am a beginner.

-Ram

 

[PHV]

What about this ?
INSERT INTO dbo_tblTracking (MRN,STUDYID) SELECT dbo_tblScheduling.MRN,dbo_tblScheduling.STUDYID
FROM dbo_tblScheduling LEFT JOIN dbo_tblTracking ON dbo_tblTracking.MRN=dbo_tblScheduling.MRN AND dbo_tblTracking.STUDYID=dbo_tblScheduling.STUDYID
WHERE dbo_tblTracking.MRN IS NULL

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