求助：在Microsoft Access中按ID筛选对应最新日期的数据

Hey there! Let's work through this query problem you're facing. You need to pull the record with the largest (latest) date value for each unique id from your Table A, right? Here are two reliable methods to make this happen in Access:

Method 1: Subquery with Join (Works in All Access Versions) #

This approach first calculates the maximum date for each id, then matches those values back to the original table to get the full record:

SELECT a.id, a.title, a.name, a.date
FROM [Table A] AS a
INNER JOIN (
    SELECT id, MAX(date) AS max_date
    FROM [Table A]
    GROUP BY id
) AS sub ON a.id = sub.id AND a.date = sub.max_date;

How it works: #

• The inner subquery groups records by id and uses MAX(date) to find the highest date value for each group.
• We then join this subquery back to the original table (Table A) using both id and date to ensure we only get the exact record that has the maximum date for each id.

Method 2: Window Function (For Access 2016+) #

If you're using a newer version of Access (2016 or later) that supports window functions, this method is clean and straightforward:

SELECT id, title, name, date
FROM (
    SELECT 
        id, title, name, date,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY date DESC) AS rn
    FROM [Table A]
) AS ranked
WHERE rn = 1;

How it works: #

• The inner query uses ROW_NUMBER() to assign a rank to each record within its id group. Records are ordered by date in descending order, so the record with the largest date gets a rank of 1.
• The outer query filters for only records where the rank (rn) is 1, giving you the latest record per id.

Quick Note: #

Since your table name has a space (Table A), make sure to wrap it in square brackets [] in your queries to avoid syntax errors in Access.

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