Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rating: Thread Rating: 12 votes, 5.00 average. Display Modes
Old 07-11-2010, 01:53 PM   #1
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,827 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
New Quick Tutorial - Exclude Data using a table

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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following 29 Users Say Thank You to boblarson For This Useful Post:
access7 (11-10-2011), Bladerunner (03-25-2013), Bob B (06-11-2011), CarlRostron (11-18-2011), Chopper83150 (11-06-2013), felicia.scott@ (07-20-2018), gaby (04-14-2011), Gemini (06-05-2014), hassanogaibi (06-28-2016), IAmNotABot (02-28-2013), im4gltr (02-12-2013), Imran Laldin (07-14-2012), jadehawk (04-26-2015), jrsaturnino (09-28-2019), kimberlin (07-29-2011), LHolden (01-25-2013), LWoods (02-22-2012), Megan (06-04-2011), MS$DesignersRCretins (12-04-2012), nigeve (03-03-2014), pattie0928 (03-24-2015), PNGBill (05-06-2012), riverstar5 (11-28-2012), Sgilmore (09-08-2019), sigma788 (11-29-2012), Simba (08-03-2011), Sketchin (07-29-2014), Timrock (03-24-2013), yaoi28lover (04-21-2014)
Old 07-11-2010, 02:35 PM   #2
PNGBill
Win10 Office Pro 2016
 
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,269
Thanks: 74
Thanked 78 Times in 74 Posts
PNGBill will become famous soon enough
Re: New Quick Tutorial - Exclude Data using a table

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.
PNGBill is offline   Reply With Quote
The Following 2 Users Say Thank You to PNGBill For This Useful Post:
hassanogaibi (06-28-2016), Timrock (03-24-2013)
Old 07-11-2010, 02:39 PM   #3
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,827 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: New Quick Tutorial - Exclude Data using a table

Quote:
Originally Posted by PNGBill View Post
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.

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
hassanogaibi (06-28-2016)
Old 09-14-2010, 03:28 AM   #4
Loranga
Registered User
 
Join Date: Sep 2010
Posts: 13
Thanks: 0
Thanked 1 Time in 1 Post
Loranga is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

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 )
Loranga is offline   Reply With Quote
The Following User Says Thank You to Loranga For This Useful Post:
hassanogaibi (06-28-2016)
Old 09-27-2010, 03:50 PM   #5
Petros
Newly Registered User
 
Join Date: Jun 2010
Posts: 145
Thanks: 7
Thanked 2 Times in 2 Posts
Petros is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

Quote:
Originally Posted by boblarson View Post
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!
Petros is offline   Reply With Quote
The Following User Says Thank You to Petros For This Useful Post:
hassanogaibi (06-28-2016)
Old 09-27-2010, 03:57 PM   #6
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,827 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: New Quick Tutorial - Exclude Data using a table

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

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

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
hassanogaibi (06-28-2016)
Old 09-30-2010, 10:06 AM   #7
Petros
Newly Registered User
 
Join Date: Jun 2010
Posts: 145
Thanks: 7
Thanked 2 Times in 2 Posts
Petros is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

I understand, thanks!

Petros is offline   Reply With Quote
The Following User Says Thank You to Petros For This Useful Post:
hassanogaibi (06-28-2016)
Old 10-14-2010, 05:03 PM   #8
mebaria
Newly Registered User
 
Join Date: Oct 2010
Posts: 9
Thanks: 0
Thanked 1 Time in 1 Post
mebaria is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

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 !!
mebaria is offline   Reply With Quote
The Following User Says Thank You to mebaria For This Useful Post:
hassanogaibi (06-28-2016)
Old 06-04-2011, 03:18 PM   #9
Megan
Newly Registered User
 
Megan's Avatar
 
Join Date: Jun 2011
Location: USA: Indianapolis, IN
Posts: 23
Thanks: 11
Thanked 2 Times in 2 Posts
Megan is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

Nice, clear tutorials you have there. I subscribed and I'm looking forward to learning more. Thank you!
Megan is offline   Reply With Quote
The Following User Says Thank You to Megan For This Useful Post:
hassanogaibi (06-28-2016)
Old 06-29-2011, 12:29 AM   #10
qurawe
Newly Registered User
 
Join Date: Jun 2011
Posts: 12
Thanks: 0
Thanked 2 Times in 2 Posts
qurawe is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

thanksssssss
qurawe is offline   Reply With Quote
The Following User Says Thank You to qurawe For This Useful Post:
hassanogaibi (06-28-2016)
Old 06-29-2011, 12:30 AM   #11
qurawe
Newly Registered User
 
Join Date: Jun 2011
Posts: 12
Thanks: 0
Thanked 2 Times in 2 Posts
qurawe is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

thanksssss
qurawe is offline   Reply With Quote
The Following User Says Thank You to qurawe For This Useful Post:
hassanogaibi (06-28-2016)
Old 07-01-2011, 11:36 PM   #12
liddlem
Newly Registered User
 
Join Date: May 2003
Location: Queensland
Posts: 332
Thanks: 20
Thanked 13 Times in 13 Posts
liddlem is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

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.
__________________
Using Win10, SQL and Access 2016.
liddlem is offline   Reply With Quote
The Following User Says Thank You to liddlem For This Useful Post:
hassanogaibi (06-28-2016)
Old 07-02-2011, 09:42 AM   #13
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,827 Times in 1,578 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: New Quick Tutorial - Exclude Data using a table

Quote:
Originally Posted by liddlem View Post
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.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
The Following User Says Thank You to boblarson For This Useful Post:
hassanogaibi (06-28-2016)
Old 11-01-2011, 09:15 AM   #14
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,800
Thanks: 636
Thanked 337 Times in 307 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Post Re: New Quick Tutorial - Exclude Data using a table

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.
Attached Images
File Type: gif Union Query Final Requested.gif (90.7 KB, 542 views)
__________________
Were you lucky enough to get an answer? Please mark your question as [SOLVED] The original poster can go to Thread Tools to mark it as Solved.

Quotation Thomas Jefferson: "Peace is that brief glorious moment in history when everybody stands around reloading."

There are 2 Kinds of Countries on this Planet
1. Those that use the Metric System
2. Those that had a man walk on the moon

Denver, Colorado - The "Mile High City" - non-metric!
Rx_ is offline   Reply With Quote
The Following User Says Thank You to Rx_ For This Useful Post:
hassanogaibi (06-28-2016)
Old 05-06-2012, 09:56 AM   #15
racolunga
Newly Registered User
 
Join Date: May 2012
Posts: 4
Thanks: 0
Thanked 1 Time in 1 Post
racolunga is on a distinguished road
Re: New Quick Tutorial - Exclude Data using a table

Thank you boblarson it's a very helpful tutorial.

racolunga is offline   Reply With Quote
The Following User Says Thank You to racolunga For This Useful Post:
hassanogaibi (06-28-2016)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to mix two tables data to prepare report? captgnvr General 38 06-25-2010 08:57 AM
Splitting one table in to multiple tables- how to move the data afterwards fuddy Tables 4 05-29-2009 05:34 PM
record ids auto number problem. jason2885 Forms 4 07-03-2007 10:16 PM
[SOLVED] Show % values with one decimal place in CHART DATA TABLE jjafer Reports 2 04-13-2005 12:29 AM
Help Saving form data to a different table bessej43 Tables 2 08-27-2002 04:41 AM




All times are GMT -8. The time now is 07:50 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World