Query to join on 5 tables

[vijip2001]

All,

I am using VB 6.0.
I am trying to join 5 tables in a query and pass the columns to crystal report through a recorset. Here is the query.
The first left join works fine.
When i add the second left join, throws error saying 'expecting a predicate'.
Can someone please help? Is there any other way to handle this?

Select test_prod.Turn_Date, test_prod.Turn, test_prod.Prod_Seq_Number,
test_prod.Roc , test_prod.Od, test_prod.Wall, test_prod.Pipe_Grade,
test_prod.Heat,test_prod.Cont_Number, test_prod.Length,
'10/21/2009 7:17:40 AM' Updated_Date, 'TEST' Updated_User,test_prod.Cont_Pieces,
test_prod.Pieces_Chg_Original, test_prod.Pieces_Chg_Rerun, test_prod.Pieces_Chg_Scrap, test_prod.Pieces_Dischg_Prime, test_prod.Pieces_Dischg_Rerun,test_prod.Pieces_Dischg_Scrap, test_prod.Pieces_Dischg_Alp_Chg, test_prod.Pieces_Left_On_Line, test_prod.Process, test_prod.Wbct, test_prod.Spacing, test_prod.Aust_Temperature, test_prod.Temper_Temperature, test_prod.Sm_Roller_Change, test_prod.Comments, test_prod.Piece_Wt, Turn_Data.Mill_Area, Turn_Data.Type_Turn, Turn_Data.Turn_Start_Time, Turn_Data.Crew, Turn_Data.Operator1, Turn_Data.Operator2, Turn_Data.Act_Equip_Hr, Turn_Data.Measured_Hr,
Turn_Data.Unmeasured_Hr, Turn_Data.Crew_Hrs, Shutdown_Data.First_Piece_Chg_Time,
Shutdown_Data.First_Piece_Dischg_Time, Shutdown_Data.Last_Piece_Chg_Time,
Shutdown_Data.Last_Piece_Dischg_Time , 'No Change' Updated_Turn_Singnal ,
reruns.schedule_number,reruns.prod_pieces
From (test_prod Inner Join Turn_Data On (test_prod.Turn = Turn_Data.Turn)
And (test_prod.Turn_Date = Turn_Data.Turn_Date)) Left Join Shutdown_Data On
(test_prod.Turn = Shutdown_Data.Turn) And
(test_prod.Turn_Date = Shutdown_Data.Turn_Date)
And (left join reruns on (test_prod.Turn = reruns.Turn) And
(test_prod.Turn_Date = reruns.Turn_Date) AND
(test_prod.prod_seq_number= reruns.prod_seq_number) )
Where (((test_prod.Turn_Date) = {d '2009-10-21'} And ((test_prod.Turn) = 'A'))
And ( Turn_Data.Mill_Area = 'TEST'))
Order By test_prod.Prod_Seq_Number;

Thanks,
Viji

 

[dhookom]

From a SQL point of view, I would question:

And  (left join reruns

I haven't seen this syntax before.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this FROM clause ?
...
FROM test_prod
INNER JOIN Turn_Data ON test_prod.Turn=Turn_Data.Turn AND test_prod.Turn_Date=Turn_Data.Turn_Date
LEFT JOIN Shutdown_Data ON test_prod.Turn=Shutdown_Data.Turn AND test_prod.Turn_Date=Shutdown_Data.Turn_Date
LEFT JOIN reruns ON test_prod.Turn=reruns.Turn AND test_prod.Turn_Date=reruns.Turn_Date AND test_prod.prod_seq_number=reruns.prod_seq_number
WHERE ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[vijip2001]

It worked.
I had an exta "AND" and a extra "(" in the query.
Thanks for all the help.

Viji