Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    21

    Unanswered: INSERT data if it doesnt exist

    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

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    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

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    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.

    Steve

  4. #4
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by sps View Post
    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.
    +1 - This is how any die hard SQL database administrators would tell you to do it. The tools exist in the engine to block constraints from entering the table (when setup properly), which can GREATLY reduce the amount of legwork you need to do when checking every insert statement.

    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •