Exceptions table

22strider

Active Member
Joined
Jun 11, 2007
Messages
311
Hello,

How can I return records from table-1 that do not match with records in table-2?

I have a master table (say Table-1) that contains all the data. Another table (Table-2) contains records that I do not want to see in the resulting table.

I need to do following:
(Table-1) - (Table-2) = Query result set.


There is a report that I have created; as the project proceeded I was asked to enter some exceptions (records that we do not want to see in the result set). So I hard coded those exceptions in my SQL query. I am thinking of creating an exceptions table to which I can add or remove exceptions without having to edit the code.

Could anyone please guide me to which approach should I follow.

Regards,
Rajesh
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
What fields are you needing to compare? This can be done in one of a few ways. Here is one possibility:

Code:
SELECT T1.*
FROM Table1 T1
LEFT JOIN Table2 T2
ON 
T1.Field1 = T2.Field1
AND
T1.Field2 = T2.Field2
AND 
(etc. for all the fields you need to compare)
WHERE T2.Field1 IS NULL

If nulls are possible in any of the fields, you will want to wrap each comparison in Nz(), for example this will work for text fields:

Code:
Nz(T1.Field1,'') = Nz(T2.Field1,'')
 
Upvote 0
Hello Jon,

Because all the fields in Table2 are also present in Table1 there should be no NULL (unless I did not understand your approach).

My goal is to return records from Table1 that are not in Table2.

Table1 is the master table that contains all records from Table2 and many more records.

Regards,
Rajesh
 
Upvote 0
Did you try using the Unmatched Query Wizard? You should be able to use it to construct a query that returns records from table1 that are not in table 2 and whose sql resembles what was posted.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top