Retrieving record details with max value date field

[ms_access_00]

I am trying to retrieve data for a particular record.

When Project field matches a certain project number I want it to pick the record with the latest date in the date field field to select certain data fields(Owner & Rating) from that record.

Below is my attempt. However the problem is that displays all records with that project number and not just the record with the latest date.

Any ideas would be much appreciated?

SELECT [Combined PRB Roadmap].ProjectNumber, Max([Combined PRB Roadmap].DateField) AS MaxOfFDateField, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
FROM [Combined PRB Roadmap]
GROUP BY [Combined PRB Roadmap].ProjectNumber, [Combined PRB Roadmap].Owner, [Combined PRB Roadmap].Rating
HAVING ((([Combined PRB Roadmap].ProjectNumber)="NR-4237"));

 

[Brianwarnock]

The max selects the max value per group, your group is every record, you need to 2 queries the first just groups on project number and max date, the second joins these fields back to the table to select the res of the data, you just run the second and the first is run by the system.

Brian

 

[ms_access_00]

Thanks for you reply.

Just to clarify what you are saying. Do I need to create two separate queries to complete my request? Is it possible just to use one query?