Need top record for each contract number

[Bonnie A]

Hi everyone. I'm using A02 on XP.

I have a query with:

SELECT [Enrollment Table].[Plan #], [Enrollment Table].[Date Received],
[Enrollment Table].New
FROM [Enrollment Table]
WHERE ((([Enrollment Table].New)=Yes))
ORDER BY [Enrollment Table].[Plan #], [Enrollment Table].[Date Received] DESC
WITH OWNERACCESS OPTION;

This pulls all the 'New' Enrollment records. There could be 560 records for
one contract and only 2 for another. I need to pull only the most recent
[DateReceived] for each [PlanNum]. Can't figure it out. Tried Max, tried
Top but sure I'm doing something wrong. Went through a few old threads but
couldn't get it.

Thank you for your time and help or advice.

 

[KARL DEWEY]

Try this ---
SELECT [Enrollment Table].[Plan #], Max([Enrollment Table].[Date Received])
AS [MaxOfDate Received], [Enrollment Table].New
FROM [Enrollment Table]
GROUP BY [Enrollment Table].[Plan #], [Enrollment Table].New
HAVING ((([Enrollment Table].New)="Yes"));

 

[Klatuu]

Go into your query builder. Click on the Totals icon. It looks like the
Greek letter sigma. You will get another row in the query builder.
Use Group by for [Plan #] and [New]
Use Max for [DateReceived]