How do I do this

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

marf - 28 May 2008 15:00 GMT

I have a TblProjects that contains details referring to a project. I have
another table called TblProjectUpdates that contains weekly updates for each
project, the realationship is one-to-many, because each project has many
updates.

This is what I need to do:
How do I create a report that retrieves information from the TblProjects
(Name, Date, #, EndDate, etc) and ONLY the last entry in TblProjectUpdates
(each entry has a date field for when the update was entered). I want to
create a report that shows the Project Info plus the Project Update Info.
Also, some projects may not have an update for that week, but I would still
like to show the last entry that does exist...

Reply to this Message

Jeanette Cunningham - 29 May 2008 04:39 GMT

marf,
create a query b ased on both tables. Use just that date field and the
primary key from tblProjects.
Change it to a totals query and choose Group By for the primary key field
and Max for the date field.
Switch to datasheet view, and you will see the most recent record for each
ProjectID.
Use this query to join to another query that has the other fields you need.
You end up with a query that shows just the info you need for the most
recent update for each project.

Jeanette Cunningham -- Melbourne Victoria Australia

>I have a TblProjects that contains details referring to a project. I have
> another table called TblProjectUpdates that contains weekly updates for
[quoted text clipped - 10 lines]
> still
> like to show the last entry that does exist...

Reply to this Message