New Quick Tutorial - Exclude Data using a table

[boblarson]

Don't know if anyone is interested but sometimes it is helpful if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).

So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.

See it here.

[PNGBill]

Thanks Bob,
On another forum (not access) we can click to add our name to a Thanks Post which says "thanks for the info, it has been helpful", without growing the thread.

[boblarson]

Originally Posted by PNGBill

Thanks Bob,
On another forum (not access) we can click to add our name to a Thanks Post which says "thanks for the info, it has been helpful", without growing the thread.

Yeah, I've seen some like that. But it just isn't set up that way here. Oh well.

[Loranga]

Hi bob,

Right now this is a little bit to complicated for me but I just wanted to say I think it's great you used the northwind databse for this example. This make it alot easier to follow. I have bookmarked your site for further reading.
My regards from a fellow viking (Larsson being a common name here in Sweden )

[Petros]

Originally Posted by boblarson

Don't know if anyone is interested but sometimes it is helpful if you have a table of data (for example, where I have a long list of investors who should not show up in my queries and I don't want to try typing a very long list in a query's IN statement).

So, I created a "Quick Tutorial" to show how easy it is to use a table to exclude data from a query.

See it here.

would it be possible to use this method to also exclude dater-anges from a query?..lets say i dont want to inlcude records "older" than 1 year to be included in my query..this to return higher performance...

Thanks!

[boblarson]

That wouldn't be necessary as you can have your criteria just be

> DateAdd("y", -1, Date())

[Petros]

I understand, thanks!

[mebaria]

Thank you for the link and showing how to do a querry. It helped me to do some reports. I tried to filter before and it does not work, it says something like the data requested is to big to be filter. This way is a lot easier to get only the info I want in the report.

Thank you !!

[Megan]

Nice, clear tutorials you have there. I subscribed and I'm looking forward to learning more. Thank you!

[qurawe]

thanksssssss

[qurawe]

thanksssss

[liddlem]

Thanks Bob
In effect, by putting in the "IsNull", you are saying "Show me all the records that DONT exist in this table". Very interesting indeed.

I was recently asked to extract data based on what is NOT known and i simply couldn't get me head around it. I suspect that this is the solution.

[boblarson]

Originally Posted by liddlem

Thanks Bob
In effect, by putting in the "IsNull", you are saying "Show me all the records that DONT exist in this table". Very interesting indeed.

I was recently asked to extract data based on what is NOT known and i simply couldn't get me head around it. I suspect that this is the solution.

I'm glad that it was helpful.

[Rx_]

Great example, I have sent it to several people. Thank you.

After using your example to create a query, I often use Union Query.
In this example, a location status can be "Requested" through "Final".
One table keeps all transactions along each status change.
The first query "-Final" simply filters on all the Final status.
The second query "-Req" filters on the Requested status - then is joined to exclude the Requested sites that were upgraded to "Final".
The second Requested query uses the same method in your demonstration.

Now that there are two queries with the same field names / field types it is easy to Union them (see attachment).
The top "-Fin-Req" Union Query shows all Finals, and only the Requested that have not been elevated to Final status.

This might be useful to anyone attempting to create their first Union query.

[racolunga]

Thank you boblarson it's a very helpful tutorial.