Re: How to query with both JOIN and aliases in Access?

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@xxxxxxxxxxxxxxxx
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?

--
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





.

Relevant Pages

  • Re: CONTAINS performance
    ... How can I predict the scalability of the simple query given that I don't ... but will measure the FTS with multiple clients issuing random FTS queries. ... B on A.bid=B.id left outer join ... the SQL Server query optimizer executes this query in the optimal manner ...
    (microsoft.public.sqlserver.fulltext)
  • Re: *=
    ... > from customers c, orders o, items i ... As Marek said *= is an old syntax for outer join. ... the query returns 5 rows. ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: how to improve performance of LEFT JOIN
    ... LEFT OUTER JOIN TableB ... I add the subquery to query every table before 'LEFT JOIN' ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (comp.databases.ms-sqlserver)
  • Re: How to query with both JOIN and aliases in Access?
    ... Access SQL recognizes Inner Join, LEFT JOIN, and RIGHT JOIN. ... It does not recognize LEFT OUTER JOIN and JOIN. ... LEFT JOIN (SELECT empID, max(datum) as LastDayWorked ... This query performs in SQL Server without any problem. ...
    (microsoft.public.access.queries)
  • Re: combining inner and outer joins
    ... the query that works for you in Sybase, and the output you're looking for. ... > I've recently switched from Sybase to SQL Server, ... > of an outer join clause. ...
    (microsoft.public.sqlserver.programming)