Selecting an Access record based on most recent date on a linked table-CodePudding

This is probably an example of an untrained person trying to get a simple outcome using the wrong tools; but...

I've got a really simple database with a Client Table and a Review Table, each entry on the Review table is linked to a ClientID. I want to view the last review for the client when I'm on a form linked to the Client table.

I think the best way to do this is to create a query. Ive tried two approaches;

SELECT TableClient.ClientID, TableClient.ClientFullName, Max(TableReviews.ReviewDate) AS LastReview
FROM TableClient INNER JOIN TableReviews ON TableClient.ClientID = TableReviews.ReviewClient
GROUP BY TableClient.ClientID, TableClient.ClientFullName;

This gives me the most recent review date, but no clear way (in my limited understanding) to get the other data from the Review table, like ReviewID or the Review Notes. whenever I try this, it just returns all the entries from the Review table. I think my problem with this approach is that I don't understand the how to use the GROUP function for an enquiry like this.

The other approach I've tried is to just query the Review table;

SELECT TableReviews.ReviewClient, TableReviews.ReviewID, Max(TableReviews.ReviewDate) AS LastReview
FROM TableReviews
GROUP BY TableReviews.ReviewClient, TableReviews.ReviewID;

This gives me all the results in my Review table, but again, no way to group/restrict the results to the most recent review for each client.

I've struggled to write an SQL statement that groups or filters properly. This seems like it should be simple, and usually this means I'm trying to solve the problem using the wrong tools. Should I be using a different approach to grab the most recent Review for a client?

many thanks.

UPDATE - I need a query that shows the most recent review for each client, so my TableReviews is:

ReviewID    ReviewDate  ReviewClient
1           17/10/2022  Johnny Smith
2           4/10/2022   Neddy Not-Here
3           13/10/2022  Johnny Smith
4           3/10/2022   Johnny Smith

and the desired result would be

ReviewID    ReviewDate  ReviewClient
1           17/10/2022  Johnny Smith
2           4/10/2022   Neddy Not-Here

CodePudding user response：

As you stated, you are using the wrong approach/tool. Try the following

SELECT TOP 1 TableClient.ClientID, TableClient.ClientFullName, TableReviews.ReviewDate
FROM TableClient INNER JOIN TableReviews ON TableClient.ClientID = TableReviews.ReviewClient
ORDER BY TableReviews.ReviewDate DESC;

TOP 1 will yield only one row and the ORDER BY clause will ensure the most recent date in ReviewDate is returned. Now you can add additional fields from TableReviews