How to remove duplicates and unwanted rows

Question

So I have a "Sample", "Test" and "Result" table linked to each other from a database and I am trying to pull information using MS Query. Each sample has one test and each test could have roughly 20 results entered by different people attached to it.

What I want is for the sample to only display if the person's name I enter is NOT involved with entering ANY of the results.

SELECT SAMPLE.SAMPLE_NUMBER, SAMPLE.TEXT_ID, SAMPLE.STATUS, SAMPLE.DATE_COMPLETED, SAMPLE.LOCATION, TEST.ANALYSIS, RESULT.ENTERED_BY

FROM DATABASE.RESULT RESULT, DATABASE.SAMPLE SAMPLE, DATABASE.TEST TEST

WHERE TEST.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER AND RESULT.TEST_NUMBER = TEST.TEST_NUMBER 
        AND ((TEST.ANALYSIS='ID_META' Or TEST.ANALYSIS='ID_RIBO' Or TEST.ANALYSIS='ID_BACTERIA' Or TEST.ANALYSIS='ID_MOULD') 
        AND (SAMPLE.STATUS='C') AND (SAMPLE.DATE_COMPLETED Is Not Null) 
        AND (RESULT.ENTERED_ON Between [Start Date] And [End Date]) 
        AND (RESULT.ENTERED_BY<>[Enter Name]))
ORDER BY SAMPLE.DATE_COMPLETED

This is the code that I have so far but the problem is if the person has entered one of 10 results then that same sample will display 9 times and just not display for the one time he didn't enter a result. Is there a way that I can say if he entered ANY result at all then the sample won't appear at all.

Answer 1

When you find yourself wanting to limit the rows by a condition that involves multiple rows (like "I want every test where none of the multiple results were entered by this person"), you can't do it with simple conditions like RESULT.ENTERED_BY<>[Enter Name]. That only looks at the value of each single row you're currently working with. You either need a correlated subquery or an analytical function. I think subqueries are easier to start out with, and in your case a NOT EXISTS clause makes intuitive sense.

(I'm also going to rewrite this with standard modern JOIN syntax)

select SAMPLE.SAMPLE_NUMBER, SAMPLE.TEXT_ID, SAMPLE.STATUS, SAMPLE.DATE_COMPLETED, SAMPLE.LOCATION, TEST.ANALYSIS, RESULT.ENTERED_BY
from DATABASE.SAMPLE SAMPLE
join DATABASE.TEST TEST
    on TEST.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER
join DATABASE.RESULT RESULT
    on RESULT.TEST_NUMBER = TEST.TEST_NUMBER 
where (TEST.ANALYSIS in ('ID_META','ID_RIBO','ID_BACTERIA','ID_MOULD')
    and (SAMPLE.STATUS='C') and (SAMPLE.DATE_COMPLETED Is Not Null) 
    and (RESULT.ENTERED_ON Between [Start Date] And [End Date]) 
-- up until here, it's the same as your query
    and NOT EXISTS (select 1 
                    from DATABASE.TEST T2
                    join DATABASE.RESULT R2
                      on R2.TEST_NUMBER = T2.TEST_NUMBER
                    where T2.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER
                      and T2.ANALYSIS in ('ID_META','ID_RIBO','ID_BACTERIA','ID_MOULD')
                      and R2.ENTERED_ON Between [Start Date] And [End Date]
                      and R2.ENTERED_BY = [Enter Name])
ORDER BY SAMPLE.DATE_COMPLETED;

So here we're saying to return all the samples where there "doesn't exist" any test with any result which was entered by the specific person. (I'm not sure whether you'll want the date filter on both the main query and the subquery - both RESULT and R2 - you'll have to figure that out based on your data.)

Edit: if you want one row per sample, just remove the TEST/RESULT joins from the main query:

select SAMPLE.SAMPLE_NUMBER, SAMPLE.TEXT_ID, SAMPLE.STATUS, SAMPLE.DATE_COMPLETED, SAMPLE.LOCATION
from DATABASE.SAMPLE SAMPLE
where (SAMPLE.STATUS='C') and (SAMPLE.DATE_COMPLETED Is Not Null) 
    and NOT EXISTS (select 1 
                    from DATABASE.TEST T2
                    join DATABASE.RESULT R2
                      on R2.TEST_NUMBER = T2.TEST_NUMBER
                    where T2.SAMPLE_NUMBER = SAMPLE.SAMPLE_NUMBER
                      and T2.ANALYSIS in ('ID_META','ID_RIBO','ID_BACTERIA','ID_MOULD')
                      and R2.ENTERED_ON Between [Start Date] And [End Date]
                      and R2.ENTERED_BY = [Enter Name])
ORDER BY SAMPLE.DATE_COMPLETED;