Max Date 2

[sjck]

I have searched the forum and tried to model my query after very similar queries, and I am having problems. I am trying to return the record with the most recent Status Date.

I have created a query StatusUpdate that combined two tables Provider and Status. Then tried to create a query to find the most current status (Code Below).

When I try to run the query I get an Enter Parameter Value box for MaxDate.

SELECT A.ProviderID, A.ProviderType, A.ProviderName, A.Speciality, A.Address, A.Address2, A.City, A.State, A.ZipCode, A.PhoneNumber, A.Extension, A.Agent, A.Contractor, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes
FROM ProviderStatus AS A INNER JOIN (SELECT ProviderID, Max(StatusDate) As MaxDate FROM tblProviderStatus GROUP BY ProviderID, MaxDate) AS M ON A.ProviderID = M.providerID AND A.StatusDate = M.MaxDate;

Any help is appreciated.

 

[PHV]

INNER JOIN (SELECT ProviderID, Max(StatusDate) As MaxDate FROM tblProviderStatus GROUP BY ProviderID[!], MaxDate[/!]) AS M

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Try this

SELECT A.ProviderID, A.ProviderType, A.ProviderName, A.Speciality, A.Address, A.Address2, A.City, A.State, A.ZipCode, A.PhoneNumber, A.Extension, A.Agent, A.Contractor, A.StatusUpdateID, A.Status, A.StatusDate, A.Notes 

FROM ProviderStatus AS A  

WHERE A.StatusDate = 
(Select MAX(StatusDate) From ProviderStatus As X 
 Where X.ProviderID = A.ProviderID)
;

 

[sjck]

Thank you so much -- I alway appreciate your help