Select Query using the "last" date group not returning correct results 1

[monkeysee]

I have a data base that tracks new private wells that have been drilled. We test these wells to make sure the quality of the well is ok. If the water results come back with coli, it is graded with a "P", if it is ok, it is graded with a "N". If the water is "P", then we must go back out at a later date to retest the well. I need to have a query that will generate a list for the wells that need to be retested. If on the second visit, the water is "N", then this well should not be included in the list.

With this sql:
SELECT WellSample.WellID, Last(WellSample.DateSampled) AS LastOfDateSampled, WellSample.[Coli Result], WellSample.Notes
FROM WellSample
GROUP BY WellSample.WellID, WellSample.[Coli Result], WellSample.Notes
HAVING (((WellSample.WellID)>0));
I get a list of all wells that have been tested, some have more than one test, the first being P, the second being N.
What I want the query to do is to filter out all wells that have tested N

I have tried this sql:
SELECT WellSample.WellID, Last(WellSample.DateSampled) AS LastOfDateSampled, WellSample.[Coli Result], WellSample.Notes
FROM WellSample
GROUP BY WellSample.WellID, WellSample.[Coli Result], WellSample.Notes
HAVING (((WellSample.WellID)>0) AND ((WellSample.[Coli Result])<>"N"));
This filters out all the "N" tests, but it still includes the "P" test even if a 2nd test was done and has come back ok (N).

Any help you can give will be GREATLY appreciated!

 

[Remou]

How about:

[tt]SELECT WellSample.WellID, Last(WellSample.DateSampled) AS LastOfDateSampled, WellSample.[Coli Result], WellSample.Notes
FROM WellSample
WHERE WellSample.WellID>0 AND WellSample.WellID Not In
(Select WellSample.WellID
From WellSample
Where WellSample.[Coli Result]<>"N")
GROUP BY WellSample.WellID, WellSample.[Coli Result], WellSample.Notes[/tt]

 

[monkeysee]

Thanks, I tried what you suggested still not returning what I need, the query now returns ALL wells that tested "N"
no "P" wells showed up which is the list I need to generate, excluding the "P" if the last test was "N"

 

[PHV]

So, you want all records where LastOfDateSampled is not "N" ?
SELECT A.WellID, B.LastOfDateSampled, A.[Coli Result], A.Notes
FROM WellSample AS A INNER JOIN (
SELECT WellID, Max(DateSampled) AS LastOfDateSampled
FROM WellSample WHERE WellID>0 GROUP BY WellID
) AS B ON A.WellID = B.WellID AND A.DateSampled = B.LastOfDateSampled
WHERE A.[Coli Result]<>'N'

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

 

[monkeysee]

We ARE getting there!

I just need to put one more condition on the query.
From Table A, field name 'SampledBy' to show only sampled by "OCHD"

how would that fit in to your sql?

 

[PHV]

...
WHERE A.[Coli Result]<>'N' [!]AND A.SampledBy='OCHD'[/!]

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

 

[monkeysee]

Thanks! I ran the query as you wrote it and it returns 25 records. In checking it, I manually counted the records it needs to return and the result is 37. Somewhere 12 records did not get into the query.

I played a little myself and this is what I came up with:

SELECT WellSample.WellID, Last(WellSample.DateSampled) AS LastOfDateSampled, WellSample.SampledBy, Last(WellSample.[Coli Result]) AS [LastOfColi Result]
FROM WellSample
GROUP BY WellSample.WellID, WellSample.SampledBy
HAVING (((Last(WellSample.DateSampled))>#1/1/2004#) AND ((WellSample.SampledBy)="OCHD") AND ((Last(WellSample.[Coli Result]))="p"));

and came up with the 37. (I put another condition (>date) in the query, but I don't think that would have anything to do with the difference in totals. Anyway this seems to work. Is there anything you can see that may be flawed in it before I put it into 'action'?

I really do appreciate your help and am giving you a star.