Issue with report - one-to-many relationship

[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...

Help!!!

 

[Jeff Boyce]

It sounds like you are describing the "maximum" ProjectUpdateDate for each
ProjNumber.

That sounds like a Totals query, grouping by ProjNumber and finding Max of
ProjectUpdateDate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[marf]

Thanks Jeff.

Can it be done in a Report?

It sounds like you are describing the "maximum" ProjectUpdateDate for each
ProjNumber.

That sounds like a Totals query, grouping by ProjNumber and finding Max of
ProjectUpdateDate.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[marf]

Jeff,

I have another question. I tried to do Totals in the query, group by
ProjNumber and max on ProjectUpdateDate. The other fields still show "group
by" does that affect anything. The query returns all entries in the
TblProjectUpdates for every ProjNumber...

Not sure what to do to fix it...

 

[Jeff Boyce]

If you are saying that your query has too many fields in it, take the
unnecessary ones out.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

Reports aren't queries.

But you can base a report on a query instead of on a table.

Regards

Jeff Boyce
Microsoft Office/Access MVP