Joining Multiple (3 or more) Tables 1

[WaterTight]

I'm trying to write the core select from clauses for this access query and am missing something. I have:

Select Distinct Patients.PatientID, Patients.FName, Patients.LName From ((Patients Left Join ON Patients.PatientID = Cases.PatientID) INNER Join Procedures ON Procedures.CaseID = Cases.CaseID) Left Join Payments ON Payments.PatientID = Patients.PatientID

I keep getting 'Syntax error in FROM clause'

I'm newer to Access and could easily write this query in SQL Server 2k:

Select Distinct a.PatientID, a.FName, a.LName From Patients a, Cases b, Procedures c, Payments d WHERE a.PatientID *= b.PatientID AND b.CaseID = c.CaseID AND a.PatientID *= d.PatientID

I think part of my problem is that I taught myself (mostly through trial and error) how do join tables. Any advice on this query or links to something that could properly teach me how to join 3 or more tables would be greatly appreciated.

 

[cmmrfrds]

I don't see Cases listed in your join tables.

Select Distinct Patients.PatientID, Patients.FName, Patients.LName
From Patients
Left Join
Cases ON Patients.PatientID = Cases.PatientID
INNER Join
Procedures ON Procedures.CaseID = Cases.CaseID
Left Join
Payments ON Payments.PatientID = Patients.PatientID

 

[WaterTight]

Ok - the more I'm playing with it, the more familiar I'm becoming with it. I did leave out Cases on my first join - however, I didn't realize that I was then trying to inner join onto a member of a outter join. My query now works with the following:

Select Distinct Patients.PatientID, Patients.FName, Patients.LName From ((Patients INNER Join Cases ON Patients.PatientID = Cases.PatientID) Left Join Procedures ON Procedures.CaseID = Cases.CaseID) Left Join Payments ON Payments.PatientID = Patients.PatientID

Any ideas how I could pull records for patients that don't have a corresponding cases record - but still pull the corresponding procedure record (that ties to the cases record) if they do?

Am I just wanting to have my cake and eat it too?

 

[r937]

<homer>
mmm, cake
</homer>

...from Patients 
  left outer join
         ( Cases
      inner join 
           Procedures 
        on Cases.CaseID 
         = Procedures.CaseID )   
    on Patients.PatientID 
     = Cases.PatientID

rudy

http://rudy.ca/

rudy

http://rudy.ca/

 

[WaterTight]

I tried to do the left outer join (cases inner join procedures) - but it said 'Join expression not supported.'

I'm guessing this is because I'm trying to inner join a member of an outer join - based primary on my attempts to do something similiar in sql server 2k query and getting an error something to that effect.

For the record, here's the code i'm trying...maybe i'm just an idiot (i.e. can you spot the flaw in my query):

Select Distinct Patients.PatientID, Patients.FName, Patients.LName from Patients left outer join ( Cases Inner Join Procedures on Cases.CaseID = Procedures.CaseID ) on Patients.PatientID = Cases.PatientID

Thanks in advance for the help...

 

[r937]

you cannot be an idiot, i have the exclusive franchise

try left outer, left outer

from ( Patients
left outer join Cases
on Patients.PatientID = Cases.PatientID )
left outer join Procedures
on Cases.CaseID = Procedures.CaseID

if, as you suggested, each case has a procedure, the second left outer will always bring back a procedure whenever the case is not null, so bob's your uncle

 

[WaterTight]

doing a left outer join on both did the trick...

just don't tell me that i'm going to have to pay you franchise fees whenever i'm an idiot...

thanks again...
J