0

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
James Clarke
  • 23
  • 5
  • Explore use of correlated subquery using TOP N. Recent question: https://stackoverflow.com/questions/74084292/ms-access-update-and-inner-join-with-multiple-rows/74084761#74084761 – June7 Oct 20 '22 at 03:19
  • @mazoula linked to the solution in the commnents below, here is my succesful query: SELECT t1.* FROM TableReviews AS t1 INNER JOIN (SELECT [ReviewClient], MAX(ReviewDate) AS LastReview FROM TableReviews GROUP BY [ReviewClient]) AS t2 ON (t1.[ReviewClient] = t2.[ReviewClient]) AND (t1.[ReviewDate] = t2.LastReview); – James Clarke Oct 24 '22 at 00:48

2 Answers2

0

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

Lybren
  • 330
  • 1
  • 10
  • Thankyou so much for the reply. this isn't quite what I'm after, as it shows the most recent review for ALL clients, and I would like it show the most recent reviews for EACH client. I'll update the question to make this clearer, sorry. – James Clarke Oct 20 '22 at 23:55
  • The answer is here: If you can't translate this to your question reply and I"ll transalte when I get the message. https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results – mazoula Oct 22 '22 at 11:13
  • Thankyou @mazoula, that was the trick, here is my succesful query: SELECT t1.* FROM TableReviews AS t1 INNER JOIN (SELECT [ReviewClient], MAX(ReviewDate) AS LastReview FROM TableReviews GROUP BY [ReviewClient]) AS t2 ON (t1.[ReviewClient] = t2.[ReviewClient]) AND (t1.[ReviewDate] = t2.LastReview); – James Clarke Oct 24 '22 at 00:47
0

Try this:

SELECT TableReviews.*
FROM TableReviews 
INNER JOIN (SELECT ReviewClient, MAX(ReviewDate) AS MaxDate 
FROM TableReviews GROUP BY ReviewClient) AS date_rec ON 
(TableReviews.ReviewClient = date_rec.ReviewClient) AND (TableReviews.ReviewDate 
= date_rec.MaxDate)
TomL123
  • 11
  • 1