troubleshooting Question

MS Access Query joining to Max record in another table

Avatar of Davisro
DavisroFlag for United States of America asked on
Microsoft AccessSQL
8 Comments1 Solution114 ViewsLast Modified:

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.EpDateBorn
WHERE ((([_NRCEbase].EbRecType)="EBAS"))
ORDER BY [_NRCEbase].EbLastName;
Join our community to see this answer!
Unlock 1 Answer and 8 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 8 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros