How do I write a query expression with MAX?

[damicomj]

Below is the SQL statement.
I am trying to group by the TICKET_NO and grab the MAX of the MAX_DATE field (which is the STATUS_DATE AND STATUS_TIME combined)

When I run the query, it keeps returning: "You tried to execute a query that does not include the specified expression 'CREATE_DATE' as part of an aggregate function."

Any help is much appreciated.

SELECT tblIncident.TICKET_NO, tblIncident.CREATE_DATE, tblStatusHistory.STATUS_DATE, tblStatusHistory.STATUS_TIME, Max([STATUS_DATE] & " " & [STATUS_TIME]) AS MAX_DATE
FROM (tblIncident LEFT JOIN tblStatusHistory ON tblIncident.IncidentID = tblStatusHistory.IncidentID) INNER JOIN tblStatus ON tblIncident.STATUS = tblStatus.STATUS
WHERE (((tblStatus.STATUS)='Active'))
GROUP BY tblIncident.TICKET_NO;
 

[patjones]

When you use GROUP BY, everything that you put in the SELECT clause must either 1) be included in the GROUP BY clause, or 2) be an aggregate function (MAX, MIN, AVG, etc.).

In your situation, CREATE_DATE, STATUS_DATE, and STATUS_TIME are not part of the GROUP BY, and so the system is complaining about that. If you remove those columns, and leave just TICKET_NO and MAX_DATE, the query should run (barring some other problem that I'm not seeing with the JOIN).

Pat

[NeoPa]

Jet SQL Help:
All fields in the SELECT field list must either be included in the GROUP BY clause or be included as arguments to an SQL aggregate function.

This quote from the Help system implies that all references to fields, even compound references, must either be aggregated (included in one of the aggregate functions - Sum(), Avg(), Min(), Max(), Count(), StDev(), Var(), First() or Last()) or included in the GROUP BY clause. Any expression which is of either type is considered aggregated. The aggregate functions can only take field reference expressions which resolve to non-aggregated fields.

Essentially, the SQL Engine needs to know how to process any particular reference. If you have a reference to a field which is not inside an Aggregate function of any type, and is also not in the GROUP BY clause, then how is the SQL Engine to know which of the many possible records in the group to take the value from.

See the situation below and ask yourself whether the result for [fB] should be "A", "B" or "C" :

SELECT   [fA]
       , [fB]
       , Sum([fC]) AS [SumC]
FROM     [Tbl]
GROUP BY [fA]

Table = [Tbl]
 [fA]  [fB]  [fC]
 1    "A"    11
 1    "B"    22
 1    "C"    33

Result :

[fA]=1, [fB]="???", [SumC]=66