Max date keeps returning multiple rows

[brown]

I'm trying to list the most recent comment for each name.

I can only get the query to return the correct data set
(i.e. the most recent row of data) when I remove
the "comment" field. It works when I chose "max" from the
drop down menu on the design page for the PerformedDate
field (note it's not a key). But, when I add back the
comment field, I get multiple rows again.

Please help. Thanks.

Here's the SQL statement that gives the right number of
rows,but it doesn't have the comment assoc. with it:
SELECT [Control list].HorseName, Max([ProcedureDetail
Table].PerformedDate) AS MaxOfPerformedDate,
[ProcedureDetail Table].PerformedBy, [ProcedureMaster
Table].ProcCodeDesc
FROM [ProcedureMaster Table] INNER JOIN ([Control list]
INNER JOIN [ProcedureDetail Table] ON [Control
list].HorseID = [ProcedureDetail Table].HorseNumber) ON
[ProcedureMaster Table].ProcCodeID = [ProcedureDetail
Table].ProcCode
GROUP BY [Control list].HorseName, [ProcedureDetail
Table].PerformedBy, [ProcedureMaster Table].ProcCodeDesc
HAVING ((([ProcedureDetail Table].PerformedBy)="DEC") AND
(([ProcedureMaster Table].ProcCodeDesc)="conformation
evaluation"))
ORDER BY [Control list].HorseName, Max([ProcedureDetail
Table].PerformedDate) DESC;

This one returns multiple rows (i.e. shows all dates):
SELECT [Control list].HorseName, Max([ProcedureDetail
Table].PerformedDate) AS MaxOfPerformedDate,
[ProcedureDetail Table].PerformedBy, [ProcedureMaster
Table].ProcCodeDesc, [ProcedureDetail Table].Comment
FROM [ProcedureMaster Table] INNER JOIN ([Control list]
INNER JOIN [ProcedureDetail Table] ON [Control
list].HorseID = [ProcedureDetail Table].HorseNumber) ON
[ProcedureMaster Table].ProcCodeID = [ProcedureDetail
Table].ProcCode
GROUP BY [Control list].HorseName, [ProcedureDetail
Table].PerformedBy, [ProcedureMaster Table].ProcCodeDesc,
[ProcedureDetail Table].Comment
HAVING ((([ProcedureDetail Table].PerformedBy)="DEC") AND
(([ProcedureMaster Table].ProcCodeDesc)="conformation
evaluation"))
ORDER BY [Control list].HorseName, Max([ProcedureDetail
Table].PerformedDate) DESC;

 

[david epsom dot com dot au]

When you include the comment field in the aggregate
query, it is grouped by comment (see 'comment' in
the group by clause).


You need to use the aggregate query to select a primary
key value, then use a select query to join the aggregate
query back to the original table, to get the comment
field.

(david)