CODEWITHSUNDEEP
sqlms-access
Chuck0185
Chuck0185

Reputation: 531

MS Access Query to Return the most recent date for each person with multiple records in a table

I have a table that consists of health plan members.

Each record represents a coverage election or a change in their coverage that includes an effective date. For instance every member has their initial plan election in January. If a member has a child in March and changes their coverage from single to individual + family, a record is created with the new effective date. The original record stays in the table and a new record with their new coverage election is created.

I need to create a query that pulls the most recent coverage for each member in the table, whether it be their original election in January or their most recent coverage change in March.

I tried a few different SQL statements but none of them worked out correctly.

The table is called tblPreviousExport. The query below returned 3 records, not even sure how it worked out that way. I would like to return the entire record for each members most recent coverage election. 

SELECT TOP 1 tblPreviousExport.[Employee SSN], tblPreviousExport.[Employee First Name], tblPreviousExport.[Employee Last Name], tblPreviousExport.[Effective Date] 
FROM tblPreviousExport 
GROUP BY tblPreviousExport.[Employee SSN], tblPreviousExport.[Employee First Name], tblPreviousExport.[Employee Last Name], tblPreviousExport.[Effective Date] 
ORDER BY tblPreviousExport.[Effective Date] DESC;

Upvotes: 1

Views: 124

Answers (1)

Lee Mac
Lee Mac

Reputation: 16015

There are many ways to achieve this -

Using a joined subquery:

One possible method is to use an inner join on a subquery that selects the maximum effective date for each Employee SSN:

select t1.*
from tblPreviousExport t1 inner join
(
    select t2.[Employee SSN], max(t2.[Effective Date]) as md
    from tblPreviousExport t2
    group by t2.[Employee SSN]
) q 
on t1.[Employee SSN] = q.[Employee SSN] and t1.[Effective Date] = q.md

Using a correlated subquery:

Another method is to use a correlated subquery which attempts a select a record with a later date than the current record, and return the records for which such subquery returns no results (represented by the where not exists clause):

select t1.*
from tblPreviousExport t1 where not exists 
(
    select 1 from tblPreviousExport t2 
    where 
    t1.[Employee SSN]   = t2.[Employee SSN] and
    t1.[Effective Date] < t2.[Effective Date]
)

Using a LEFT JOIN with unequal join criteria:

Finally, you could also use unequal join criteria with a left join in the following way, returning records for which there are no records on the right of the join which meet the join criteria:

select t1.* 
from 
    tblPreviousExport t1 left join tblPreviousExport t2 on 
    t1.[Employee SSN]   = t2.[Employee SSN] and
    t1.[Effective Date] < t2.[Effective Date]
where 
    t2.[Effective Date] is null

This example can only be represented in MS Access in the SQL view, as the MS Access Query Designer cannot display joins which have equal join criteria (i.e. where one field equals another).

This example is similar in operation to the correlated subquery, but the selection is performed by the join, rather than within the WHERE clause.

Upvotes: 2

Related Questions