How to query with both JOIN and aliases in Access?

[Oscar]

I am converting the following query from SQL Server to MS Access.
The following query fires a 'missing operator' message within my VB IDE.


strSQL = "Select w.empID " _
& " FROM tabEmp w INNER JOIN tblArbCtr c ON w.empID = c.wrnID WHERE " &
selStr & " AND c.contract <> 0 AND c.datumStart <= " & datumStr & " AND
c.datumEind >= " & datumStr & " LEFT OUTER JOIN (SELECT empID, max(datum)
AS LastDayWorked FROM tabGewerkt GROUP BY empID) v ON w.empID = v.empID
LEFT OUTER JOIN (SELECT wrnID,min(datumStart) AS FirstDayWorked FROM
tblArbCtr WHERE contract <> 0 GROUP BY wrnID) v2 ON w.empID = v2.wrnID "

It looks like Access can't cope with the aliases v and v2 after the
parantheses, but I'm not sure about that and don't know how it needs to be
written for MS Access.. Please help.

Oscar

 

[Ofer Cohen]

Hi, few things

strSQL = "Select w.empID " _
& " FROM tabEmp w INNER JOIN tblArbCtr c ON w.empID = c.wrnID WHERE " &

' Here you have AND strait after the WHERE, you need the first condition
not to include AND before it

selStr & " AND c.contract <> 0 AND c.datumStart <= " & datumStr & " AND
c.datumEind >= " & datumStr & "

' How did you get from WHERE conditions back to the link between the tables?
LEFT OUTER JOIN (SELECT empID, max(datum)
AS LastDayWorked FROM tabGewerkt GROUP BY empID) v ON w.empID = v.empID
LEFT OUTER JOIN (SELECT wrnID,min(datumStart) AS FirstDayWorked FROM
tblArbCtr WHERE contract <> 0 GROUP BY wrnID) v2 ON w.empID = v2.wrnID "

Not sure how this SQL worked in the SQL Server,
Can you post the view you had on the SQL server before you started making
changes to it?

 

[Oscar]

hi Ofer,

this is the original SQL Server query :

strSQL = "Select w.empID, w.naam , c.ID, v.LastDayWorked,v2.FirstDayWorked "
_
& " FROM tblEmployee w JOIN tblArbCtr c ON w.empID = c.wrnID AND templ=0 AND
c.contract <> 0 AND c.datumStart <= " & datumStr & " AND (c.datumEind >= " &
datumStr & " OR c.datumEind IS NULL) LEFT OUTER JOIN (SELECT empID,
max(datum) LastDayWorked FROM tabWorked GROUP BY empID) v ON w.empID =
v.empID LEFT OUTER JOIN (SELECT wrnID,min(datumStart) FirstDayWorked FROM
tblArbCtr WHERE contract <> 0 GROUP BY wrnID) v2 ON w.empID = v2.wrnID "

This query performs in SQL Server without any problem. Hope you can help.

regards

Oscar

 

[John Spencer]

Access SQL recognizes Inner Join, LEFT JOIN, and RIGHT JOIN. It does
not recognize LEFT OUTER JOIN and JOIN.

Access requires that multiple joins use parentheses.

Access uses # as the date delimiter

Access require field aliases to be preceded with the word AS.

Taking all that into account, the following may work for you. If not
you may have to move the following into a where clause instead of
including it in the JOIN

AND templ=0 AND

= #" & datumStr & "# OR c.datumEind IS NULL

strSQL = "
SELECT w.empID, w.naam , c.ID, v.LastDayWorked, v2.FirstDayWorked
FROM ((tblEmployee w INNER JOIN tblArbCtr c " & _
' ON w.empID = c.wrnID AND templ=0 AND

= #" & datumStr & "# OR c.datumEind IS NULL))

LEFT JOIN (SELECT empID, max(datum) as LastDayWorked
FROM tabWorked
GROUP BY empID) v
ON w.empID = v.empID) LEFT JOIN
(SELECT wrnID, min(datumStart) as FirstDayWorked
FROM tblArbCtr
WHERE contract <> 0 GROUP BY wrnID) v2 ON w.empID = v2.wrnID "

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================