Easy query question......

[ag90fox]

I have a "project status" table with multiple records for each project distinguished by date.

RecID, ProjID, Date, Status

I want to take the most recent record for each project and show it on a form. I've tried to group on ProjID where I take the "max" date, but when I tell the query how to choose the status row, it seems to pull a random value, not the status tied directly to that date.

Can anyone tell me how to do this. I think it should be easy, but here I am looking for help!)

 

[swampBoogie]

select * from projectStatus ps
where dateColumn in
(select max(dateColumn) from projectStatus
where projID = ps.projID)

Are you really using an Access database?

 

[Trendsetter]

Use a normal select query and sort on Date in descending order now when you open the form that is based on this query the record on screen will be the latest date.

 

[ag90fox]

SWAMPBOOGIE....Why do you ask if I'm using access?