MS Access查询以返回表中具有多个记录的每个人的最新日期 [英] 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.

每条记录代表一项承保范围的选择或其承保范围的变化(包括生效日期).例如，每个成员都在一月份进行了最初的计划选举.如果成员在3月生了一个孩子，并将其覆盖范围从单身家庭改为个人+家庭，则会创建一个具有新生效日期的记录.原始记录保留在表中，并创建具有新覆盖率选择的新记录.

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.

我尝试了一些不同的SQL语句，但是没有一个能正确解决.

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

该表称为tblPreviousExport.下面的查询返回了3条记录，甚至不确定该如何计算.我想返回每个成员最近一次选举的全部记录.

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;

推荐答案

有很多方法可以实现-

一种可能的方法是在子查询上使用inner join，该子查询选择每个Employee SSN的最大生效日期:

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

---

使用相关子查询:

另一种方法是使用相关的子查询，该子查询尝试选择一个日期比当前记录晚的记录，并返回该子查询不返回任何结果的记录(由where not exists子句表示):

---

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]
)

---

使用具有不相等加入条件的LEFT JOIN:

最后，您还可以通过以下方式对left join使用不相等的联接条件，返回在联接右侧没有满足联接条件的记录的记录:

---

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

此示例只能在SQL视图的MS Access中表示，因为MS Access查询设计器无法显示具有相等联接条件(即，一个字段等于另一个字段)的联接.

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).

此示例的操作与相关子查询相似，但是选择是通过联接执行的，而不是在WHERE子句中进行的.

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

这篇关于MS Access查询以返回表中具有多个记录的每个人的最新日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！