Select last in a text field in a group by

In MS Access SQL, I'm using

select max(id), last(text)  
from sms  
group by client

On a table with sms messages sent between the client and us in the effort of displaying the last message in the chat with the max id (id is an autoincremented column). The max id is returning correctly but the last text is not returning the last text.

I’ve read back and forth and see that last isn't always working as expected. But I for sure know that max(id) is actually the last inserted id. But last returns an older one.

For some reason, the "last" id is not the real last id inserted into the table. I'm using max for the id and it works. But can't use max for text as it's useless.

I'd like to understand why last id is not returning the last (max) id. And, how would I work around getting the actual text for the max id.

I tried some joins and such in the effort of selecting text where id = max(id) but couldn't get it to work, I guess syntax was wrong.

For example:

select 
    max(id) as idm, 
    (select text from sms where id = idm) 
from sms 
group by client

For each group of client, you want to find the max id and its corresponding text.

I have just changed your tried approach to a subquery where id is the max(id) for a client group.

SELECT  sms.client,sms.id,sms.text
FROM sms
WHERE sms.id = 
(SELECT MAX(sms1.id) FROM sms AS sms1 WHERE sms1.client = sms.client);

This is an example fiddle in Mysql, but overall syntax should be same for ms-access.
Otherwise if the syntax does not work, you can also try the inner join approach I added in fiddle.

Above generates

for sample input data