LEFT JOIN ON MORE THAN TWO TABLE IN EXCEL - PLS HELP

[Yuvraj desai]

Hi, Not sure about syntax for third left join, Getting Syntax error "missing operator" in query expression


strSQL = "SELECT [OMS$].[REGION], [OMS$].[SITE],([LOP$].[Total Volume Shipped] + [LOP$].[Total Volume Received]), " & _
"[LABP$].[LAB],[REVP$].[REV]" & _
"FROM ([OMS$] LEFT JOIN [LOP$] ON [OMS$].[SITE] = [LOP$].[Site]) " & _
"LEFT JOIN [LABP$] ON [OMS$].[SITE] = [LABP$].[SITE] AND " & _
"LEFT JOIN [REVP$] ON [OMS$}.[SITE] = [REVP$].[SITE] " & _
"GROUP BY " & _
"[OMS$].[REGION], [OMS$].[SITE],([LOP$].[Total Volume Shipped] + [LOP$].[Total Volume Received]),[LABP$].[LAB]," & _
"[REVP$].[REV]"

 

[Chihiro]

There's syntax error below.

"LEFT JOIN [LABP$] ON [OMS$].[SITE] = [LABP$].[SITE] AND " & _
"LEFT JOIN [REVP$] ON [OMS$}.[SITE] = [REVP$].[SITE] " & _

You don't need "AND" to join multiple.

For an example to join 3 tables.

Select
    t1.Column1 as Col1,
    t1.Column2 as Col2,
    t2.Column3 as Col3,
    t3.Column4 as Col4
From
    Table1 as t1
Left Join Table2 as t2
On t1.Column1 = t2.Column1
Left Join Table3 as t3
On t1.Column2 = t3.Column3

Edit: Also noticed below. You don't need "()"

"FROM ([OMS$] LEFT JOIN [LOP$] ON [OMS$].[SITE] = [LOP$].[Site]) " & _

 

[Yuvraj desai]

Hi, I did the changes but again getting "missing operator" syntax error in highlighted code

strSQL = "SELECT t1.[REGION] as Col1 , t1.[SITE] as col2 ,(t2.[Total Volume Shipped] + t2.[Total Volume Received]) as Col3, " & _
"t3.[LAB] as Col4 ,t4.[REV] as col5 " & _
"FROM [OMS$] as t1 LEFT JOIN [LOP$] as t2 ON t1.[SITE] = t2.[SITE] " & _
"LEFT JOIN [LABP$] as t3 ON t1.[SITE] = t3.[SITE] " & _
"LEFT JOIN [REVP$] as t4 ON t1.[SITE] = t4.[SITE] " & _
"GROUP BY " & _
"t1.[REGION], t1.[SITE],(t2.[Total Volume Shipped] + t2.[Total Volume Received]),t3.[LAB]," & _
"t4.[REV]"

 

[Yuvraj desai]

Problem resolved !!! finally below code worked

Private Sub SHOW_Click()

strSQL = "SELECT [OMS$].[REGION],[OMS$].[SITE],([LOP$].[Total Volume Shipped] + [LOP$].[Total Volume Received]), " & _
"[LABP$].[LAB], [REVP$].[REV] " & _
"FROM (([OMS$] LEFT JOIN [LOP$] ON [OMS$].[SITE] = [LOP$].[SITE]) " & _
"LEFT JOIN [LABP$] ON [OMS$].[SITE] = [LABP$].[SITE]) " & _
"LEFT JOIN [REVP$] ON [OMS$].[SITE] = [REVP$].[SITE] " & _
"GROUP BY " & _
"[OMS$].[REGION], [OMS$].[SITE],([LOP$].[Total Volume Shipped] + [LOP$].[Total Volume Received]),[LABP$].[LAB]," & _
"[REVP$].[REV]"

closeRS

OpenDB

rs.Open strSQL, cnn, adOpenKeyset, adLockOptimistic
If rs.RecordCount > 0 Then
Sheets("BASE").Visible = True
Sheets("BASE").Select
Range("RAMP").Select
Range(Selection, Selection.End(xlDown)).ClearContents

'Now putting the data on the sheet
ActiveCell.CopyFromRecordset rs
Else
MsgBox "Records not found.", vbExclamation + vbOKOnly
Exit Sub
End If


End Sub

Missing operator was "() " every extra left join should be accompanied by bracket starting after 'From'

 

[Yuvraj desai]

Thanks Chihiro for helping to scratch my Excel brain