tblEMPLOYEES has FIRSTNAME, LASTNAME, EMPILID, DATETRAINED, DEPT
tblMAIN has EMPILID, OBSERVATIONDATE
In tblMAIN, the EMPILID is not the primary key, and each ID can have several dates. I only want the last date each ID has an Observation, and then to join that with the tblEMPLOYEES data and essentially get a recordset of each employee, and their last observation date. I was playing around with some queries and this is what I have so far, but I get an error in the FROM section.
Expand|Select|Wrap|Line Numbers
- SELECT EMP.EMPILID, EMP.FIRSTNAME, EMP.LASTNAME, EMP.DATETRAINED, EMP.DEPT, EMP.LASTOBSDATE
- FROM tblEMPLOYEES AS EMP INNERJOIN
- (SELECT MAIN.EMPILID, MAX(MAIN.OBSERVATIONDATE) AS LASTOBSDATE
- FROM tblMAIN AS MAIN
- GROUP BY MAIN.EMPILID) AS COMB ON EMP.EMPILID = COMB.EMPILID
- GROUP BY EMP.EMPILID
Can anyone see where I'm going wrong?