Skip to content
Advertisement

Data exclusions based on two-tables MS Access

I have table A which includes all data based on a read date. For example:

Read Date----Data
1/1/2016-----3
1/2/2016-----10
1/3/2016-----42
1/4/2016-----16
12/25/2016----32
12/26/2016----12
12/27/2016-----8

From that data set, I created a query that looks at a list of off-peak holidays, as well as off peak days, and created a table from that. This table looks like:

Read Date ----- Data
1/1/2016---------3
1/2/2016---------10 
12/25/2016-------32

I need to create a query that will give me table A data EXCLUDING table B data. Which would look something like this:

Read Date ----- Data
1/3/2016---------42
1/4/2016---------16
12/26/2016-------12
12/27/2016--------8

I have tried different join, NOT IN, WHERE NOT, and many others, which would sadly just return all results from Table A or no results at all.

PS – I apologize for the formatting, new to all of this.

NOT IN, WHERE NOT, <>, LEFT JOIN, ISNULL

Advertisement

Answer

Use not exists:

select a.*
from a
where not exists (select 1
                  from b
                  where b.read_date = a.read_date and b.data = a.data
                 );
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement