Thread: INSERT data if it doesnt exist
06-12-12, 20:32 #1Registered User
- Join Date
- Feb 2012
Unanswered: INSERT data if it doesnt exist
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.
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:
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:
and if it doesnt have
Then when I save the form scheduling with MRN = 1234 and Studyid = 2
The Tracking table should also automatically have the following data:
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)"
'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.
06-13-12, 08:57 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
I would use the DLookup Function to see if a Record already exists for the given Data, and conditionally run the Insert Statement or not.
What, exactly, is the purpose of the Tracking Table? Given the Fields you've posted for it as well as for your Scheduling Table, it would appear that they hold identical Data, a violation of the rules of Relational Databases.
Or are there additional Fields in the Tracking Table that you failed to list?
Linq ;0)>Hope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007
06-13-12, 16:24 #3Registered User
- Join Date
- Aug 2004
- Cary, NC
Given the fields that you listed in your tables the combination of MRN & StudyID is unique (otherwise the tables wouldn't be of much use as listed)
If you set a unique index for those two fields you can use a simple insert query, similar to the one you use to insert the record into the scheduling table, and let the database kick out any duplicates.
06-15-12, 05:33 #4Registered User
- Join Date
- Jun 2011
- Inside your mind
It also means that you can transition your database to mySQL or something similar, without needing to write your own versions of MS-Only commands, like DLOOKUP.Looking for the perfect beer...