MS Access query - Extract records with most recent date 2

[BxWill]

Have a MS Access Select query as displayed below that results in the extraction of data with multiple records per member with different dates.

What modifications need to be made to just extract one record for each member - the record with the most recent date?

Thanks in advance for any assistance. Just can't quite get this right after more than 2 hours!


SELECT EligMbrs_SecondIter.MEMBER_ID, dbo_MEMBER_PCP.PROVIDER_ID, dbo_MEMBER_PCP.MBR_PCP_BEGIN_DT, dbo_PROVIDER.PROV_FULL_NM
FROM (dbo_MEMBER_PCP INNER JOIN dbo_PROVIDER ON dbo_MEMBER_PCP.PROVIDER_ID = dbo_PROVIDER.PROVIDER_ID) INNER JOIN EligMbrs_SecondIter ON dbo_MEMBER_PCP.MEMBER_ID = EligMbrs_SecondIter.MEMBER_ID;

Query Results currently look like this;

MemberID---ProviderID---MbrPCPBegDt----ProvName
12345-------90567--------1/1/08---------JaneSmith
12345-------90456--------3/4/08---------JillSmith

 

[HarleyQuinn]

Have a look at MAX() and GROUP BY aggregate functions.

Hope this helps

HarleyQuinn
---------------------------------
You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before posting.

http://lessthandot.com

 

[PHV]

Use either this:

SELECT E.MEMBER_ID, M.PROVIDER_ID, M.MBR_PCP_BEGIN_DT, P.PROV_FULL_NM
FROM ((dbo_MEMBER_PCP M
INNER JOIN dbo_PROVIDER P ON M.PROVIDER_ID = P.PROVIDER_ID)
INNER JOIN EligMbrs_SecondIter E ON M.MEMBER_ID = E.MEMBER)
INNER JOIN (SELECT MEMBER_ID,Max(MBR_PCP_BEGIN_DT) AS LastDate FROM dbo_MEMBER_PCP GROUP BY MEMBER_ID
) L ON M.MEMBER_ID = L.MEMBER_ID AND M.MBR_PCP_BEGIN_DT = L.LastDate

or this:

SELECT E.MEMBER_ID, M.PROVIDER_ID, M.MBR_PCP_BEGIN_DT, P.PROV_FULL_NM
FROM (dbo_MEMBER_PCP M
INNER JOIN dbo_PROVIDER P ON M.PROVIDER_ID = P.PROVIDER_ID)
INNER JOIN EligMbrs_SecondIter E ON M.MEMBER_ID = E.MEMBER
WHERE M.MBR_PCP_BEGIN_DT = (SELECT Max(M.MBR_PCP_BEGIN_DT) FROM dbo_MEMBER_PCP WHERE MEMBER_ID=M.MEMBER_ID)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886