0

This is related to my other post as I try to teach myself SQL in Access.

I have two tables, TableReviews & TableClient, and I'm trying to write a query that gives me the ID of most recent review for each client. I went back to basics and crawled through W3C School. I wrote the following query, which worked on equivalent tables & field types in the W3C sample database.

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

When I try to view the query in datasheetview, I get the following error: "Your query does not include the specified expression 'ReviewID' as a part of an aggregate function." I think I understand what this means, but not why it should cause an error. I've dropped the query into two different syntax checkers (https://en.rakko.tools/tools/36/ and https://www.coderstool.com/sql-syntax-checker) and they both say it's valid SQL.

Is there something specific in the Access version of SQL that I don't understand?

Ken White
  • 123,280
  • 14
  • 225
  • 444
James Clarke
  • 23
  • 5
  • 3
    That's not valid SQL, so your title is wrong. All columns in the SELECT that are not part of an aggregate function must be included in the GROUP BY clause. `TableNavViews.ReviewID` is not in the GROUP BY, which is what causes the error. You would really benefit from a good SQL book or tutorial - this is basic SQL functionality that is in all SQL, Access or not. – Ken White Oct 24 '22 at 00:25
  • 1
    There's a good explanation why this is required [in this answer](https://stackoverflow.com/a/71085678/62576). – Ken White Oct 24 '22 at 00:40
  • 1
    There are no syntax (punctuation, keywords, clause order) errors but still not entirely valid SQL. Your other question already has solutions for returning most recent record for each client. – June7 Oct 24 '22 at 01:38

0 Answers0