Query Help - use of MAX returns all rows for an ID - I want only one 1

[skerr]

I have the following table:

ReqStatusID AutoNumber Unique Identifier for table
RequestID Number Unique Identifier for the Request Table
StatusID Number Unique Identifier for the Status Description Table
StatusDte Date/Time Date status added for a request
Detail Text Detail for status added for a request

As a new phase of a process is completed for a particular request, a new record is added to this table. Depending on the number of phases in a process, this table could hold several records for the same RequestID.

I would like a query that will pull the most current phase of a RequestID, with the results listing for each RequestID the Last StatusDte and the related StatusID. Oh, and the Status ID cannot equal 90, 26 or 3.

I tried to accomplish this by pulling RequestID and the MAX of the StatusDte, and the results were one record for each RequestID, but as soon as I add StatusID (with criteria) the query then pulls every record for each RequestID.

I would appreciate any help with this. Thanks.

 

[Golom]

Try
[blue][tt]
Select RequestID, StatusID, StatusDte

From tbl T

Where StatusDte =

(Select MAX(X.StatusDte) From tbl X
WHERE X.RequestID = T.RequestID
AND X.StatusID NOT IN (3, 26, 90) )
[/tt][/blue]

 

[skerr]

Worked perfectly - thank you.

I do have one more issue with this query. I now realize that the results of this query should not include any RequestID that appears in another query (Completed Requests).

Any further advice would really be appreciated.

Thanks.