Problems Customising A Query

[hoppergs]

I have 2 tables: one that stores details of projects (project name, manager, sponsor, etc) and the other stores status reports that are basically project updates. Each project record will potentially have many of these updates.

What I want to do is create a query that will display only the last status report submitted for each project. I've tried all combinations of Grouping and using Max, Last, etc but so far no joy!

Does anyone have a simple solution for this?

Thanks, Graham

 

[muneco]

It all depends on how your tables are set up and the details of what you want returned by the query, but here is one suggestion. If the tables are set up something like this:

PROJECT
proj_id (PK)
proj_name
proj_sponsor
proj_mgr

STATUS_RPT
proj_id (FK)
status_date
status_text


SELECT p.*, s.status_date, s.status_text
FROM status_rpt AS s, project AS p
WHERE s.proj_id=p.proj_id AND S.STATUS_DATE = (SELECT MAX(STATUS_DATE) FROM STATUS_RPT AS X WHERE X.PROJ_ID = S.PROJ_ID);


I hope this helps. So many others with superior expertise have helped me on this forum, and improved upon my suggestions, I'm just trying to pitch in where/when I can. Thanks to all...

 

[hoppergs]

This seems to do the trick! Thanks very much for your help - this has been driving me nuts for 2 days!!!

Graham