MS Access Query joining to Max record in another table
I'm trying to query two tables,
1. An employee base record table (_Ebase) (contains no duplicates)
2. An employee personal information table (ePerson)
ePerson has multiple records per employee because a new record is created every time they change address, but I only want the latest record from the ePerson side.
For the full data set, I would join the tables on the eBase table unique ID for the employee (called FlxID in this system) where eBase.ebflxID = ePerson.epFlxIdEb (the Eb suffix means its the foreign key for EbFlxID)
How do I set up the query to only pull
1. Everyone from the eBase side
2. but only the last record from the ePerson side for each employee?
Intuitively, it would be the Max of the unique ID for the table (ePerson.EpFlxID) for each employee. I don't necessarily need the solution to be in Sql form (using the query design interface is fine also) but pasting the Sql here is easy to do so,
SELECT [_NRCEbase].EbFlxID, NRCEPerson.EpFlxID, [_NRCEbase].EbRecType, "19660" AS co, [_NRCEbase].EbClock AS id, "I" AS [Record Type], [_NRCEbase].EbLastName AS [Last Name], [_NRCEbase].EbFirstName AS [First Name], [_NRCEbase].EbMiddleName, Left([_NRCEbase]![EbSocNumber],3) & "-" & Mid([_NRCEbase]![EbSocNumber],4,2) & Right([_NRCEbase]![EbSocNumber],4) AS SSN, [_NRCEbase].EbDateBeg, NRCEPerson.EpDateBornFROM _NRCEbase INNER JOIN NRCEPerson ON [_NRCEbase].EbFlxID = NRCEPerson.EpFlxIDEbWHERE ((([_NRCEbase].EbRecType)="EBAS"))ORDER BY [_NRCEbase].EbLastName;
SELECT Table1.Field1, qryMax.MaxOfscore
FROM Table1 INNER JOIN qryMax ON Table1.ID = qryMax.personID;
SELECT Table2.personID, Max(Table2.score) AS MaxOfscore
GROUP BY Table2.personID;