asked on MS Access Query joining to Max record in another table
Experts,
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,
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
FROM _NRCEbase INNER JOIN NRCEPerson ON [_NRCEbase].EbFlxID = NRCEPerson.EpFlxIDEb
WHERE ((([_NRCEbase].EbRecType)="EBAS"))
ORDER BY [_NRCEbase].EbLastName;
Microsoft AccessSQL
Last Comment
Davisro
sorry main query has a left join
SELECT Table1.Field1, qryMax.MaxOfscore
FROM Table1 LEFT JOIN qryMax ON Table1.ID = qryMax.personID;
SELECT Table1.Field1, qryMax.MaxOfscore
FROM Table1 LEFT JOIN qryMax ON Table1.ID = qryMax.personID;
if you want the last record (as opposed to the highest score) do max on the id (which was defined as autonumber) and group by person id.
Can you tell me what issue are you facing with the query you have written?
Try this .. SOLUTION 1
or Solution 2..
Enjoy !!
SELECT r.* , S.EpDateBorn FROM
(
SELECT
`E`.EbFlxI`, S.EpFlxID, `E`.EbRecTyp`, `19660` AS co, `E`.EbCloc` AS id, `I` AS [Record Type],
`E`.EbLastNam` AS [Last Name], `E`.EbFirstNam` AS [First Name], `E`.EbMiddleNam`, Left(`E`![EbSocNumber],3)
& `-` & Mid(`E`!`EbSocNumber`,4,2) & Right(`E`!`EbSocNumber`,4) AS SSN, `E`.EbDateBe`
,(SELECT MAX(EpFlxIDEb) MaxEpFlxIDEb
FROM NRCEPerson N
WHERE E.personID = N.personID
) AS MaxEpFlxIDEb
FROM
_NRCEbase E
)r INNER JOIN NRCEPerson re ON r.MaxEpFlxIDEb = re.EbFlxID
or Solution 2..
SELECT
[E].EbFlxID, S.EpFlxID, [E].EbRecType, "19660" AS co, [E].EbClock AS id, "I" AS [Record Type],
[E].EbLastName AS [Last Name], [E].EbFirstName AS [First Name], [E].EbMiddleName, Left([E]![EbSocNumber],3)
& "-" & Mid([E]![EbSocNumber],4,2) & Right([E]![EbSocNumber],4) AS SSN, [E].EbDateBeg
,(SELECT N.EpDateBorn
FROM NRCEPerson N
WHERE E.personID = N.personID
ORDER BY N.EbFlxID DESC
LIMIT 1
) AS EpDateBorn
FROM
_NRCEbase E
Enjoy !!
ASKER
Will these approaches work if I need multiple columns, say 7 or 8, from the second table?
The task actually requires me to populate a template with 40 or so fields coming from 5 tables, 4 of of which are the many side of a 1 to many relationship. So, would it be better to create new queries for each of the 4 left joined tables that filter for only the records i need form those tables and then join them?
The task actually requires me to populate a template with 40 or so fields coming from 5 tables, 4 of of which are the many side of a 1 to many relationship. So, would it be better to create new queries for each of the 4 left joined tables that filter for only the records i need form those tables and then join them?
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Thanks
SELECT Table1.Field1, qryMax.MaxOfscore
FROM Table1 INNER JOIN qryMax ON Table1.ID = qryMax.personID;
qrymax
SELECT Table2.personID, Max(Table2.score) AS MaxOfscore
FROM Table2
GROUP BY Table2.personID;