如何在Access中关联邮件对应的最近后续回复日期？

问题背景 #

现有两张Access表：

1. 收件数据表 [Client Recieved Filtered data]，结构及数据：

2. 回复发件数据表 [sent email filtered data]，结构及数据：

需求：关联两张表，为每一条收件记录找到发送时间晚于收件时间的最近回复日期。

原SQL的问题 #

你之前的SQL使用了ABS(DateDiff)，这会把收件时间之前的回复也纳入时间差计算，导致匹配到不符合要求的回复；同时子查询的关联逻辑过于复杂，进一步放大了逻辑偏差，最终结果无法满足需求。

正确的SQL写法 #

方法1：关联子查询（简洁版，仅取回复时间） #

SELECT
    RE.[From] AS ReceivedFrom,
    RE.[Received] AS ReceivedDate,
    (
        SELECT TOP 1 SE.[Sent]
        FROM [sent email filtered data] AS SE
        WHERE SE.[To] = RE.[From]
          AND SE.[Sent] > RE.[Received]
        ORDER BY SE.[Sent] ASC
    ) AS NextReplyDate
FROM [Client Recieved Filtered data] AS RE;

方法2：LEFT JOIN子查询（适合需要更多回复字段的场景） #

SELECT
    RE.[From] AS ReceivedFrom,
    RE.[Received] AS ReceivedDate,
    SE.[To] AS SentTo,
    SE.[Sent] AS NextReplyDate
FROM [Client Recieved Filtered data] AS RE
LEFT JOIN (
    SELECT
        SE1.[To],
        SE1.[Sent],
        RE1.[Received]
    FROM [sent email filtered data] AS SE1
    INNER JOIN [Client Recieved Filtered data] AS RE1
        ON SE1.[To] = RE1.[From]
    WHERE SE1.[Sent] > RE1.[Received]
    AND NOT EXISTS (
        SELECT 1
        FROM [sent email filtered data] AS SE2
        WHERE SE2.[To] = SE1.[To]
          AND SE2.[Sent] > RE1.[Received]
          AND SE2.[Sent] < SE1.[Sent]
    )
) AS SE
    ON RE.[From] = SE.[To]
    AND RE.[Received] = SE.[Received];

逻辑说明 #

1. 核心过滤条件：添加SE.[Sent] > RE.[Received]，确保只匹配收件时间之后的回复，排除所有早于收件时间的无效数据。
2. 取最近回复：通过ORDER BY SE.[Sent] ASC + TOP 1（或NOT EXISTS排除更早的回复），精准获取符合条件的最早回复，也就是距离收件时间最近的后续回复。
3. LEFT JOIN保障完整性：即使某条收件记录没有对应的后续回复，该记录仍会被保留，NextReplyDate字段将显示为Null。

内容的提问来源于stack exchange，提问作者silentninja89