Field Value in Query Based on Other Fields (1 Viewer)

[MaryD]

Hi all

It's been a while since I used Access and I'm currently tying myself in knots with a query on data imported from another system. I can code a little, but in this case I'm trying to just use the query builder and can't quite get the results I want.

Simplified version - Fields are PersonId, StartDate and ApplicationId (unique).

The personId can be in the table multiple times. I'm trying to find the person's latest start date and the corresponding ApplicationID. So I'm grouping by personId and selecting Max for StartDate, but I'm struggling to get it to return the applicationID for that date.

Person Id	Start Date	Application Id
1234	1st April 2023	121212
1234	5th May 2023	678987
1234	11th October 2022	145687
Group By	Max	?????

And I want it to return

1234

5th May 2023

678987

Hope that makes sense - any an all input much appreciated!!!!

Mary

 

[June7]

One way is with a correlated subquery using TOP N. Review http://allenbrowne.com/subquery-01.html#TopN

Another is to JOIN table to aggregate query that returns Max(StartDate) for each person.

SELECT table.*, MDTE FROM table INNER JOIN (SELECT PersonID, Max(StartDate) AS MDTE FROM table) AS Q ON table.PersonID = Q.PersonID AND table.StartDate = Q.MDTE;

You show dates formatted with ordinals - dates need to be actual dates in a date/time field, not text.

 

[MaryD]

Thanks June7, dates are formatted as dates, listed them as ordinals to rule out any confusion with US/European date format. I'll give it a go, Access skills very rusty but I'm starting to remember how much I enjoyed it!!