Query Help

[genxiii]

hey guys. I need some help on a query that I'm have a lot of problems with.

I have the following two tables. There's a 1 to many relationship between the two tables.

Project

ID Project
=== =======
1 Testing
2 Planning
3 Development

Status
ID StatusDate Status
=== ========== =======
1 10/30/03 New Project
1 11/30/03 In Progress
2 10/30/03 New Project
2 11/30/03 In Progress
3 11/30/03 In Progress
3 12/30/03 Completed

What I need to do is run a query where I will be able to pull the latest status date and status of each project along with the project name and ID. The current results lists all the record twice with the corresponding statusDate and Status but i wanted it to list the ID and Project status only once along with the latest status date and the corresponding status for that date.

Desired Result.

ID Project StatusDate Status
=== ======= ========== ======
1 Testing 11/30/03 In Progrress
2 Planning 11/30/03 In Progress
3 Development 12/30/03 Completed

I've tried using Max(StatusDate) and group by, but group by only works if i group by ID, but that only works if i hav ID and StatusDate showing and i need all the fields to be displayed. Any help would be much appreciated. Thanks

 

[dhookom]

Create a query that joins the two tables on the ID field. Set a criteria under the StatusDate column
(SELECT Max(StatusDate) FROM Status S WHERE S.ID = Project.ID)

Duane
MS Access MVP

 

[Golom]

You need a correlated sub-query for this. Try something like

   SELECT P.ID, P.Project, 
          MAX(S.StatusDate) As [LatestDate],
          (Select Top 1 X.Status From Status X 
           Where  X.ID = P.ID
           ORDER BY X.StatusDate DESC) As [Status]
   FROM Project P INNER JOIN Status S ON P.ID = S.ID
   GROUP BY P.ID, P.Project