0

Currently I have an Access table that has 3 columns:

PersonID, Code, Date

A PersonID can have a different code on a different day, I would like to only present the latest code on the latest day.

Full Table:

img

The result from the query I am looking for:

Columns

I have tried to add MAX criteria to the data column but it does not seem to work:

MAX([Date])
Shadow
  • 33,525
  • 10
  • 51
  • 64
John
  • 965
  • 8
  • 16
  • The link explains how to display the top 3 records, I need the query to show only the latest code based on the date – John Sep 13 '19 at 03:03
  • @Rene Can you please show how this can be displayed my data, its a bit different – John Sep 13 '19 at 03:10

1 Answers1

1

There are multiple ways to do this. The first one is most likely the easiest. This is the exact same idea as from the link that Rene posted in the comments.

You need to use a subquery in the where clause to get the most recent date with the MAX function.

SELECT *
FROM [Table]
WHERE Date = (SELECT MAX(T1.Date) FROM [Table] AS T1 WHERE T1.PersonID = Table.PersonID)

In a more complicated version, you could also get the same result with an inner join on itself:

SELECT
           TABLE.PersonID
         , TABLE.Code
         , MaxDate
FROM
           [Table]
           INNER JOIN
                      (
                               SELECT
                                        PersonID
                                      , Max(DATE) AS MaxDate
                               FROM
                                        [Table]
                               GROUP BY
                                        PersonID
                      )
                      AS T1
                      ON
                                 (
                                            TABLE.PersonID = T1.PersonID
                                            AND TABLE.Date = T1.MaxDate
                                 )

And you obtain the following :

PersonID    Code    Date
115         38833   8/14/2019
117         38838   6/13/2018
hisoka
  • 344
  • 3
  • 13
  • Both of the queries seem to present the max twice each, so I get 8/14/2019 twice and 6/13/2018 twice. I am using your exact query so I am not sure where the problem comes from – John Sep 13 '19 at 11:56
  • That is only possible if you have the max dates duplicated in your data table. If `PersonID` and `Date `don't form a primary key then it's going to return all the max dates for the same `PersonID`. Double check the data in your table. This cannot happen with the sample data you posted. – hisoka Sep 14 '19 at 13:50