>I have these tables:
>
>EMPMAST
>
>IDNO SURNAME
>0001 Reyes
>0002 Roberts
>0003 Argent
>0004 Wesley
>
>ACTION
>
>IDNO EFFDATE DESC
>0001 01/01/1996 Hire
>0001 01/01/1997 Promote
>0001 01/01/1998 Increase Wage
>0001 01/01/1999 Transfer
>0002 01/01/1997 Hire
>0002 01/01/1999 Promote
>
>What I want to display is:
>
>IDNO SURNAME EFFDATE DESC
>0001 Reyes 01/01/1999 Transfer
>0002 Roberts 01/01/1999 Promote
>
>How do I query this? I am using Access 97. Thank you for your help :)
Try this:
SELECT Action.Idno, EmpMast.Surname, Last(Action.Desc) AS DernierDeDesc, Max(Action.EffDate) AS MaxDeEffDate
FROM EmpMast INNER JOIN Action ON EmpMast.IDNO = Action.Idno
GROUP BY Action.Idno, EmpMast.Surname;
Éric Moreau, MCPD, Visual Developer - Visual Basic MVP
Conseiller Principal / Senior Consultant
Moer inc.
http://www.emoreau.com