Hello
I thought this seemed fairly straightforward, but it's giving me fits and driving me a little crazy.
Data as such (tablename=clientnotes)
client type date
smith email 20Jan2020
jones phone 2Feb2020
smith phone 2Feb2020
smith phone 19Mar2020
jones email 14Apr2020
wilson phone 14Apr2020
wilson email 4Oct2020
smith email 6Nov2020
I am attempting to create a query that returns the last date each client was contacted via phone
The query using the above data would return:
smith phone 19Mar2020
jones phone 2Feb2020
wilson phone 14Apr2020
The criteria set to 'phone' is straightforward. I've various iterations of using a "Totals" query in query designer and set Total to 'Last' for date or set Total to 'Last' for client.
SQL looks like:
SELECT ClientNotes.client, Last(ClientNotes.[Date]) AS [LastOfDate], ClientNotes.[Type]
FROM ClientNotes
GROUP BY ClientNotes.client, ClientNotes.[Type]
HAVING (((ClientNotes.[Type])="phone"))
ORDER BY ClientNotes.client;
I tried messing with TOP 1 but that ends up only returning a single client, not all clients. I feel like I'm over-thinking it and have confused myself.
I really appreciate any assistance.
Steve