I have a database that has information on status of employee's. I'm trying to write a SQL statement that will select only information from the Most recent date. I can get this to work with a simple script, but when I attempt to add the rest of the information in, Trouble!

SELECT Max(qryOpen_Action.Rec_Date) AS aRec_Date, qryOpen_Action.StaffID
FROM qryOpen_Action
GROUP BY qryOpen_Action.StaffID;

This script only uses two fields currently, but I need quite a few more. other fields I want to integrate are, Action, Department, Position, ROP. I do NOT want all the fields grouped tho. I can get it to work by MAX'n the Rec_Date & having all the other fields grouped, but that is not how I need the data. I just want the most Recent Date for the different StaffID's & the other accompanying data! Is there a way to avoid grouping everything together?

Thanks MUCH!

This should work for you:

select q.StaffID, q.field, q.field from qryOpen_Action q where q.Rec_Date = (SELECT max(qry.Rec_Date) from qryOpen_Action qry)

hth

"The one language all programmers understand is profanity."