Access SQL Query: Return values not between an interval of related tables

0
votes

I have 2 tables (Intervals and Depths) related by the Name field. I want the query to return all Depths that are Not in the Intervals table for each Name (or not equal to or between the Top and Bottom). My query fails when there are multiple Name records in the Intervals table (example: there are 2 'one' records in the Name field).

Intervals

Name    Top    Bottom
one     2      3
one     5      7
two     2      3
three   3      4

Depths

Name    Depth
one     1
one     2
one     3
one     4
one     5
one     6
one     7
one     8
two     1
two     2
two     3
two     4
two     5
three   1
three   2
three   3
three   4
three   5

My Query:

SELECT Intervals.Name, Depths.Depth
FROM Depths INNER JOIN Intervals ON Depths.Name = Intervals.Name
WHERE (((Depths.Depth) < [Intervals]![Top] 
    Or (Depths.Depth) > [Intervals]![Bottom]))
ORDER BY Intervals.Name, Depths.Depth;

I know this fails because the Where clause is applied individually to each Name record in Interval. The Where clause should apply for All Interval records related by Name so the result does not include any Top to Bottom intervals in the Interval table.

My Query Output: 

Name    Depth
one     1
one     1   
one     2    
one     3
one     4    
one     4
one     5
one     6
one     7
one     8   
one     8
three   1
three   2
three   5
two     1
two     4
two     5

Desired Output: All depths NOT in intervals

Name    Depth   
one     1       
one     4       
one     8       
two     1
two     4
two     5
three   1
three   2
three   5
1
sqlms-accessbetween

1 Answers

2
votes

The phrasing of your question suggests not exists, so this might work for you:

select d.*
from depths as d
where not exists (select 1
                  from intervals as i
                  where i.name = d.name and d.depth between i.[top] and i.[bottom]
                 );