SELECT in a JOIN query results in 'Syntax Error in FROM clause'

[Oscar]

This is my case :

First, the query should find which employees have a contract and add a
record for each employee ID
After that it should add a column which holds the value of the last date at
which the employee has worked or is empty in case the employee has not
worked yet.

concerned tables and fields are :
tblEmp.IDemp
tblContracts.IDemp
tblWorked.IDemp
tblWorked.Date

My query is :

SELECT tblEMP.IDemp AS WID,dateL FROM tblEmp AS w INNER JOIN (SELECT
MAX(tblWorked.date) AS dateL FROM tblWorked AS G WHERE G.IDemp=w.IDemp) ON
w.IDemp = tblContracts.IDemp "

In my VB app the compiler fires a 'Syntax Error in FROM clause'. How should
I rewrite the SELECT part of this query ?

 

[John Spencer]

Two possible Solutions.

SELECT tblContracts.IdEmp
, tblEmp.IdEmp
, LD.LastDate
FROM (TblContracts INNER JOIN tblEmp
On TblContracts.IdEmp = tblEmp.IdEmp)
LEFT JOIN
(SELECT IdEmp, Max(tblWorked.Date) as LastDate
FROM tblWorked
GROUP BY IdEmp) as LD
ON tblEmp.IdEmp = LD.IdEmp

Another solution would be to use a coordinated subquery in the SELECT Clause

SELECT TblEmp.IdEmp
, (SELECT Max(tblWorked.Date)
FROM tblWorked
WHERE tblWorked.IdEmp = tblEmp.IdEmp) as LastDate
FROM tblEmp


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

 

[Oscar]

Thanks John for your quick reply.
I've copied your query into my application, changed the table and field
names to match the original database and find that it still fires this
'Syntax Error in FROM Clause'

This is the 'real' query :

"SELECT tblArbctr.werknemerID, tabWerknemer.werknemerID, LD.LastDate FROM
(tblArbCtr INNER JOIN tabWerknemer On tblArbCtr.wrnId =
tabWerknemer.werknemerId) Left Join (SELECT tabGewerkt.werknemerID,
Max(tabGewerkt.Datum) as LastDate from tabGewerkt GROUP BY
tabGewerkt.werknemerID) as LD ON tabWerknemer.werknemerId = LD.werknemerID "


where :
tblArbCtr --> tblContracts
tabWerknemer --> tblEmp
tabGewerkt --> tblWorked

Since the database is a MS Access 97 which is driven by ADO 2.7, could the
problem be the database version?

Regarding your second solution, It doesn't have a restriction that the
employee should have a contract

regards,

Oscar

 

[John Spencer]

You have the following in the join clause

On tblArbCtr.wrnId = tabWerknemer.werknemerId

I suspect that should read

On tblArbCtr.wrnnemerId = tabWerknemer.werknemerId


SELECT tblArbctr.werknemerID
, tabWerknemer.werknemerID
, LD.LastDate FROM
(tblArbCtr INNER JOIN tabWerknemer
On tblArbCtr.wrknemerId = tabWerknemer.werknemerId)
Left Join (
SELECT tabGewerkt.werknemerID,
Max(tabGewerkt.Datum) as LastDate
FROM tabGewerkt
GROUP BY tabGewerkt.werknemerID) as LD
ON tabWerknemer.werknemerId = LD.werknemerID

As for the second solution, perhaps you should add the contracts table
into the query in the FROM clause. That would return employees that had
ANY contract.


SELECT TblEmp.IdEmp
, (SELECT Max(tblWorked.Date)
FROM tblWorked
WHERE tblWorked.IdEmp = tblEmp.IdEmp) as LastDate
FROM tblEmp INNER JOIN tblContracts
ON tblEmp.IDEmp = TblContracts.IDEmp



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

 

[Oscar]

Hi John,

By not optimal database design the field for employeeID in tblArbCtr reads
wrnID. Hence the error is still there.

The second solution runs like a charm. I didn't know that it was possible to
emulate a LEFT JOIN in this way.
thanks for your help again!

regards,
Oscar

 

[John Spencer]

That is interesting. You refer to tblArbctr.WerkNemer in the SELECT
clause and to tblArbCtr.WrnID in the Join Clause.

As long as you have a solution, I guess we won't worry about the table
and field names and what relates to what.

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