Power Tip: Exclude data from a query by using a table

Seems like a long way around when you could just use the query in the first place.

Bonnie - how would you use a query to make up an arbitrary list to be used as filter? Besides, a frustrated join has the potential to be optimized whereas a NOT IN() cannot be.

Very difficult.

Instead, I'd use ListBox to select unwanted records and then dynamically create the IN part of the query for the form/report. I could even store IDs of selected items in an INI-file to be used later. No extra tables needed...

And so let's say you have been given a list of companies to exclude in an Excel workbook and there are 200 entries. Are you goint to spend the time to manually enter all of those in a NOT IN statement or go select all 200 of them in a listbox? Not practical. There are plenty of times when this is very useful.

Oh, and also just another thing you can do - at work, we typically can have a lot of large queries. We will use this method with a subquery which we attach into things to either select what we want or exclude what we want using a query that has been created. Many times we use this because we have some memo fields that are needed or perhaps we want to have an outer join with another query and, of course, you cannot have criteria on the fields that are on the many side of an outer join.

The example Bob posted is perhaps too simple to be a hit with this audience, but we use the same basic idea all the time. For complicated filtering scenarios it's very efficient. We often create a permanent local table for storing lists of ID's to be excluded via left outer joins; a delete query clears the data from that 'temp' table and an append query repopulates it. Sometimes the records to excluded are 'hand picked' via a listbox; more often they're generated by a code routine that relies on form based controls to supply criteria for the filter. Bob's later posts here in which he cites the real world scenarios for this approach line up very well with our own approach.

Great sample, great tips to build other more complex tasks, thanks for sharing!

This is one of the issues I resolve with a "tag" checkbox - a field I check for including or excluding records that don't match some other logical pattern. For instance, I may get a number of newsletters returned by the Post Office - I do the necessary research to update the addresses (if possible), check the "tag" field, and run a mailing query on just the "tagged" records. After processing those records, I run an "untag" query.