Query unique values with top value

[diegomarino]

hi,
i have this table called people in which i have many messages from many people

i want the result as in "popleQuery" in which i have only the last message from the person, so it is a unique value in which i have only one message per person, and it is the latest message.


how may i do this?
thanks
https://hhfinanza-my.sharepoint.com/...H8dUg?e=j6dHc5

[Bob Fitz]

Create a "Totals" query. Group By your people field. Select Max for your date field.

For more specific help, we would need the names of your table and the relevant fields.

[CJ_London]

perhaps something like

SELECT People.*
FROM People INNER JOIN (SELECT PersonID, Max(mDate) as MaxDate FROM People GROUP BY PersonID) M ON People.PersonID=M.PeopleID AND People.mDate=M.MaxDate

[diegomarino]

it was supereasy. i made GroupBy for person, position and company, max for message date and last for message content..
so i have only one person (i groupedby also position and company for omonimia case) with the latest message and the last content message.
i think it's correct
thanks
ps great forum, the best for access i'll be a usual customer

[CJ_London]

be aware, last depends on order. It is not a guarantee to be the last message entered (or for that user).

[diegomarino]

this is my query. my real interest is to get the latest date message (data messaggio in italian), i choose "last" in messaggio (message) field just cause i had to select an aggregation, i don't mind very much to have the last message, i care to have every persons just once and the last date of message if they sent me a message..
thanks