INNER JOIN and MAX between two tables

[ewokspy]

I have 2 tables that I need to use an INNER JOIN on.

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.

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

I know the select to get the last observation is correct as I ran just that select command and it worked fine, just the join is the issue it seems, and I am fairly n00bish at SQL.

Can anyone see where I'm going wrong?

[Stewart Ross]

Hi. It is simpler to use the Access query editor to create the query for you, adding the two tables to the editor window, dragging the fields across. Use View Totals to allow grouping and calculations, and use Max for your observation date, giving the calculated field an appropriate alias name.

The resulting SQL does not use a subquery:

SELECT tblEMPLOYEES.EMPILID, tblEMPLOYEES.FIRSTNAME, tblEMPLOYEES.LASTNAME, tblEMPLOYEES.DATETRAINED, tblEMPLOYEES.DEPT, Max(tblMAIN.OBSERVATIONDATE) AS MAXOBSDATE
FROM tblEMPLOYEES INNER JOIN tblMAIN ON tblEMPLOYEES.EMPILID = tblMAIN.EMPILID
GROUP BY tblEMPLOYEES.EMPILID, tblEMPLOYEES.FIRSTNAME, tblEMPLOYEES.LASTNAME, tblEMPLOYEES.DATETRAINED, tblEMPLOYEES.DEPT;

-Stewart