I already have most of the query I am working with correct
Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.Status
From Clients Inner Join
Status On Clients.rid = Status.rid
Where (Status.Status In (12, 19) Or Status.Status = 29) And Status.wave = 9 And
Status.StatusDate = (Select Max(Status.StatusDate) From Status
Where Clients.rid = Status.rid And)
I only want results to turn up in my query for those with status 29, 19, or 12 on the most recent date, and not those with a higher value more recently i.e. 40, 42 etc. My issue is that when 40 and and 29 for example happen on the same date the person comes up in my query, even though I am only interested in that person if their most recent value is 40. So I would like to select a person in the query if their status is 29 on the most recent date, but only if 12 or 19 or 29 is their max status, not if the max status is anything higher than that. I tried this but I'm not sure of the syntax, I've been getting an error message.
Select Clients.rid, Clients.r_fname, Clients.r_lname, Status.Status
From Clients Inner Join
Status On Clients.rid = Status.rid
Where (Status.Status In (12, 19) Or Status.Status = 29) And Status.wave = 9 And
Status.StatusDate = (Select Max(Status.StatusDate) From Status
Where Clients.rid = Status.rid And Max(Status.Status) = 29 or 19 or 12)
Any help would be appreciated, thanks!