Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2012

    Unanswered: SQL doubt "Select within same table"

    I have three tables:

    tblPatient (MRN, Name ) (Primary key is MRN )
    tblPatientStudy (ID, MRN, STUDYID, Eligible) (Primary key is ID)
    tbltracking (ID, MRN, STUDYID, STATUS, Tracking date) ( Primary key is ID)

    A patient can be in any number of studies and each study has status.
    MRN is unique Medical record number for a patient
    Studyid values are 1 to 10
    Studystatus = 1 to 5
    Eligible = Yes or No

    tblpatient is linked to tblPatientStudy ( one to many relation )
    tblpatient is linked to tbltracking ( one to many relation )

    If I input a particular STUDYID it should pull all the patients who are enrolled in that particular study and also all other studies that particular patient is enrolled and its status ( study status )
    Please provide me a SQL query that will address the above.

    I am using MS ACCESS 2010 and this query will be used behind the report.Thanks in advance !

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    I think that this will need a subquery.

    The subquery will take the Study ID parameter and return a list of all patient MRNs on that study.

    The list of MRNs is then used in the WHERE clause of a query that joins your three tables and returns patient name, study IDs and each study's status.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Feb 2012
    SELECT A.MRN, B.NewStatus, B.DateScheduleStart, B.DateScheduleEnd, C.brochuremaildate, C.OffHolddate, tblCARTOX_II_STATUS.DateLetterSend, tblCARTOX_II_STATUS.Status, tblPatientStudy.ISELIGIBLE, tblPatientStudy.ISENROLLED, tblStudies.STUDYNAME, B.StudyID
    FROM (TblA_MainBaseTable AS A INNER JOIN tblPatientStudy AS tblPatientStudy_1 ON A.MRN = tblPatientStudy_1.MRN) INNER JOIN ((((dbo_tblscheduling AS B INNER JOIN dbo_tblTracking AS C ON (B.MRN = C.MRN) AND (B.StudyID = C.StudyID)) LEFT JOIN tblCARTOX_II_STATUS ON B.MRN = tblCARTOX_II_STATUS.MRN) INNER JOIN tblPatientStudy ON (B.MRN = tblPatientStudy.MRN) AND (B.StudyID = tblPatientStudy.STUDYID)) INNER JOIN tblStudies ON tblPatientStudy.STUDYID = tblStudies.STUDYID) ON tblPatientStudy_1.MRN = B.MRN
    WHERE (((tblPatientStudy_1.STUDYID)=[PLEASE ENTER STUDY ID]))

Posting Permissions

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