My job role has changed rapidly since the person who created our system left and has offered no help since. In the last 9 months, I've had to learn VBA, SQL, and do my usual general IT work. I'm an SQL newbie really.
I can get my head around most basic queries, however, this one is making me loopy. Consider the code below.
SELECT ClientRef, CustomerID, dbo.vwFrmLegals.ActionDate As [Last Legal Date], LegalActionName As [Last Legal Action] FROM dbo.vwFrmCustomers INNER JOIN dbo.vwFrmLegals ON dbo.vwFrmLegals.PlaintID = dbo.vwFrmCustomers.PlaintID INNER JOIN dbo.vwListLegalActions ON dbo.vwListLegalActions.LegalActionID = dbo.vwFrmLegals.LglActionID WHERE ( (dbo.vwFrmCustomers.PlaintID > 0) AND (dbo.vwFrmCustomers.FileClosed Is NULL) ) ORDER BY CustomerID
This returns all the data I need, and a lot I don't. I only want the most recent record returned based on the date in "Last Legal Date". I've Googled, but none of the solutions seem to work for me. When I use MAX(), I only get the one singular latest record (one record returned).
Can anyone explain to me how to achieve one record per CustomerID, which would be the one with the most recent "Last Legal Date"?