Re: How to query with both JOIN and aliases in Access?
- From: John Spencer <spencer@xxxxxxxxxx>
- Date: Sun, 15 Apr 2007 17:28:07 -0400
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
c.contract <> 0 AND c.datumStart <= #" & datumStr & "# AND (c.datumEind >= #" & 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
c.contract <> 0 AND c.datumStart <= #" & datumStr & "# AND (c.datumEind >= #" & 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
'====================================================
Oscar wrote:
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
"Ofer Cohen" <OferCohen@xxxxxxxxxxxxxxxxxxxxxxxxx> schreef in bericht news:ECDCDAAD-0325-4C7F-8355-716484FF4A73@xxxxxxxxxxxxxxxxHi, 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?
--
Good Luck
BS"D
"Oscar" wrote:
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
- References:
- Prev by Date: RE: Return records in one table, not in another
- Next by Date: Access97 on XP - Error 2287
- Previous by thread: Re: How to query with both JOIN and aliases in Access?
- Next by thread: Re: Show all rows...Please help...
- Index(es):
Relevant Pages
|
