question about totals query and max value

[marf]

I have a table called TblProjects that contains all details related to the
project, I have a table called TblProjectUpdates which contains a weekly
update for each project. The relationship is one-to-many

TblProjectUpdates
UpdateID - auto
ProjNumber - foreign key
ProjectUpdate
ProjectUpdateDate


I need to create a report that shows ONLY the latest (based on
ProjectUpdateDate)
entry in the TblProjectUpdates table for every project in TblProjects. Right
now the report only shows the 1st entry in the TblProjectUpdates for each
project. Also, it could be that a project does not have an update for several
weeks, I still want to show the latest entry...

Someone already suggested that I use a Totals query....
When I choose Totals from the view menu, all the fields show "group by," and
I select max on the ProjectUpdateDate field. However, all the other fields
still show "group by" and the query still returns all the entries for every
project. I only want to see the latest entry in TblProjectUpdates for each
project.

Help!

 

[Arvin Meyer [MVP]]

Build 2 queries. The first will be a Totals query with ProjectNumber and
ProjectUpdateDate. Once you have the ProjectNumber and max date you can join
query that to another query that has all the details. Join on both fields
(ProjectNumber and ProjectUpdateDate).