Generate List with MaxofDate & LastofColiSample with more criteria 1

[monkeysee]

My office tests new water wells for drinking safety. I have generated a query that gives me a list of all wells that were tested by our agency that have a test result of "P" or "PP" or "CL2"

What I want it to do in addition to this is when a particular well is again tested and the result is now "N" or "NA" or "NR", that well needs to drop off the list. my fields are such:

WellID
WellPermitNo with criteria >"2004-000"
MaxofDateSampled
LastofColiSample with criteria: Like "p*" or "CL2"
SampledBy

what do I need to add to this query to have the list drop the well when it reaches the "N*" status?

 

[PHV]

What is your actual SQL code ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[monkeysee]

Sorry, I guess that would have helped. Here it is.

SELECT NewSample.WellID, WellPermit.WellPermitNo, Max(NewSample.DateSampled) AS MaxOfDateSampled, SampledBy.SampledBy, NewSample.Count
FROM SampledBy RIGHT JOIN (NewSample INNER JOIN WellPermit ON NewSample.WellID = WellPermit.WellID) ON SampledBy.SamplerID = NewSample.SampledByID
WHERE (((NewSample.[Coli Result]) Like "p*"))
GROUP BY NewSample.WellID, WellPermit.WellPermitNo, SampledBy.SampledBy, NewSample.Count
HAVING (((WellPermit.WellPermitNo)>"2004-000") AND ((NewSample.Count) Is Not Null))
ORDER BY WellPermit.WellPermitNo DESC , Max(NewSample.DateSampled) DESC;

 

[PHV]

What about something like this ?

SELECT N.WellID, W.WellPermitNo, Max(N.DateSampled) AS MaxOfDateSampled, S.SampledBy, N.Count
FROM ((NewSample N
INNER JOIN WellPermit W ON N.WellID = W.WellID)
LEFT JOIN SampledBy S ON N.SampledByID = S.SamplerID)
LEFT JOIN (SELECT WellID FROM NewSample WHERE [Coli Result] In ('N','NA','NR')
) A ON N.WellID = A.WellID
WHERE N.[Coli Result] In ('P','PP','CL2') AND A.WellID Is Null
AND W.WellPermitNo>'2004-000' AND N.Count Is Not Null
GROUP BY N.WellID, W.WellPermitNo, S.SampledBy, N.Count
ORDER BY 2 DESC , 3 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[monkeysee]

Excellent! Exactly what I need and works great! Sorry I hadn't replied sooner - office has been having State audits!

Thanks again, you were a great help!