Link to home
Create AccountLog in
Avatar of Davisro
DavisroFlag for United States of America

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,

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;

Open in new window

Avatar of COACHMAN99
COACHMAN99

try
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;
sorry main query has a left join

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

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

Open in new window



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

Open in new window


Enjoy !!
Avatar of Davisro

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?
ASKER CERTIFIED SOLUTION
Avatar of Pawan Kumar
Pawan Kumar
Flag of India image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Davisro

ASKER

Thanks