The goal is to remove all the bad fax numbers from a JBlast fax list. This applies to any situation where you want to remove one list of data from another list of data. Another way to say it is that you have a full list, and you're trying to remove a sublist from the full list.

The way you do it is by taking the full list, and then doing a JOIN that will add a column that identifies the rows that are present in the sublist. You can do this with a LEFT JOIN. A LEFT JOIN includes all the rows in the left table, in this case, the full list. It matches on a key in the right table, and when there's a match, columns from the right table are included; when there's no match, the columns are set to NULL.

The following image shows a left join in Access.

The left join is indicated by the arrow connecting the two tables. To create a left join, you drag column names to create a relationship line, then right click on the line and edit it.

The SQL is (sorta): SELECT name,fax,error FROM full_list LEFT JOIN sublist ON full_list.fax=sublist.fax WHERE error IS NULL OR error='Line Busy'

To create a new list that includes only the rows NOT in the sublist, you filter so that you bring in NULLs. In this specific example, we're looking for NULLs and "Busy" numbers. Everything else is considered a bad number.

The output of this query can be saved out as a new list.

(Correction: For some reason, the raw undelivered.csv file has a space in front of each phone number. I had to create an extra query to remove it. I'll post the fix later.)