LEFT OUTER JOIN THREE TABLES 1

[Annelies]

Hi

I have been going slightly batty over this all day and would really appreciate any help that anyone can offer.

I have three tables in Access:

MILESTONE DATA (WBS, Tranche ID, Milestone Number, ...)
MILESTONE NUMBERS (WBS, Tranche ID, Milestone Number, ...)
TRANCHE DATA (WBS, Tranche ID, ...)

Milestone Numbers is the table that I need to Outer Join - the info will definetly exist in Milestone Data and Tranche Data, but may or may not exist in Milestone Numbers.

I want to join these tables using OUTER joins and return various fields from each.

I currently have:

SELECT MD.[Milestone Name]
FROM [Milestone Data$] as MD
LEFT JOIN ([Milestone Numbers$] as MN
LEFT JOIN [Tranche Data$] as TD
      ON (TD.WBS = MN.WBS) and (TD.[Tranche ID] = MN.[TRANCHE ID))
       ON (MN.WBS = MD.WBS) AND( MN.[Tranche ID] = MD.[Tranche ID]) AND (MN.[Milestone Number] = MD.[Milestone Number]);

I've also tried:

SELECT [Milestone Data$] .[Milestone Name]
FROM ([Milestone Number$] RIGHT JOIN [Milestone Data$]  on (([Milestone Number$].WBS =  [Milestone Data$].WBS) AND ([Milestone Number$].[Tranche ID] =[Milestone Data$].[Tranche ID]) AND ([Milestone Number$].[Milestone Number] = [Milestone Data$].[Milestone Number])))
   RIGHT JOIN [Tranche Data$] on (([Tranche Data$].WBS = [Milestone Number$].WBS) and ([Tranche Data$] .[Tranche ID] = [Milestone Number$].[TRANCHE ID));

...with no luck. I get a "Syntax error in JOIN expression" message.

I've come up with what seems like a million varations of this thing I've come up with and I'm going no-where fast! I'm not very familiar with OUTER joins so any help that anyone can offer would be much appreciated.

Thanks so much

Annelies

 

[PHV]

And what about this ?
SELECT MD.[Milestone Name]
FROM ([Milestone Data$] AS MD
INNER JOIN [Tranche Data$] AS TD ON MD.WBS=TD.WBS AND MD.[Tranche ID]=TD.[TRANCHE ID])
LEFT JOIN [Milestone Numbers$] AS MN ON MD.WBS=MN.WBS AND MD.[Tranche ID]=MN.[Tranche ID] AND MD.[Milestone Number]=MN.[Milestone Number];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886