SQL doubt SELECT within same table ?

[ramindya]

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 )

Doubt/question:
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 !

[orange]

A Patient can be enrolled in 1 or more Studys
There are 10 possible Studys
Each Patient has a Status related to each Study in which he/she is enrolled

I see these tables:

Patient ----------->PatientStudy<-------------------Study

Table PatientStudy would include a field PatientStudy Status and Statuses would be in a Lookup Table.

I have no idea what Eligible represents, and I'm not sure if you are really asking a question.