07-11-2010, 01:53 PM
|
#1
|
Smeghead
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
|
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.
|
|
|
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)
|
07-11-2010, 02:35 PM
|
#2
|
Win10 Office Pro 2016
Join Date: Jul 2008
Location: Tokoroa, New Zealand
Posts: 2,269
Thanks: 74
Thanked 78 Times in 74 Posts
|
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.
|
|
|
The Following 2 Users Say Thank You to PNGBill For This Useful Post:
|
|
07-11-2010, 02:39 PM
|
#3
|
Smeghead
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
|
Re: New Quick Tutorial - Exclude Data using a table
Quote:
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.
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to boblarson For This Useful Post:
|
|
09-14-2010, 03:28 AM
|
#4
|
Registered User
Join Date: Sep 2010
Posts: 13
Thanks: 0
Thanked 1 Time in 1 Post
|
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  )
|
|
|
The Following User Says Thank You to Loranga For This Useful Post:
|
|
09-27-2010, 03:50 PM
|
#5
|
Newly Registered User
Join Date: Jun 2010
Posts: 145
Thanks: 7
Thanked 2 Times in 2 Posts
|
Re: New Quick Tutorial - Exclude Data using a table
Quote:
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!
|
|
|
The Following User Says Thank You to Petros For This Useful Post:
|
|
09-27-2010, 03:57 PM
|
#6
|
Smeghead
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
|
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.
|
|
|
The Following User Says Thank You to boblarson For This Useful Post:
|
|
09-30-2010, 10:06 AM
|
#7
|
Newly Registered User
Join Date: Jun 2010
Posts: 145
Thanks: 7
Thanked 2 Times in 2 Posts
|
Re: New Quick Tutorial - Exclude Data using a table
I understand, thanks!
|
|
|
The Following User Says Thank You to Petros For This Useful Post:
|
|
10-14-2010, 05:03 PM
|
#8
|
Newly Registered User
Join Date: Oct 2010
Posts: 9
Thanks: 0
Thanked 1 Time in 1 Post
|
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 !!
|
|
|
The Following User Says Thank You to mebaria For This Useful Post:
|
|
06-04-2011, 03:18 PM
|
#9
|
Newly Registered User
Join Date: Jun 2011
Location: USA: Indianapolis, IN
Posts: 23
Thanks: 11
Thanked 2 Times in 2 Posts
|
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!
|
|
|
The Following User Says Thank You to Megan For This Useful Post:
|
|
06-29-2011, 12:29 AM
|
#10
|
Newly Registered User
Join Date: Jun 2011
Posts: 12
Thanks: 0
Thanked 2 Times in 2 Posts
|
Re: New Quick Tutorial - Exclude Data using a table
thanksssssss
|
|
|
The Following User Says Thank You to qurawe For This Useful Post:
|
|
06-29-2011, 12:30 AM
|
#11
|
Newly Registered User
Join Date: Jun 2011
Posts: 12
Thanks: 0
Thanked 2 Times in 2 Posts
|
Re: New Quick Tutorial - Exclude Data using a table
thanksssss
|
|
|
The Following User Says Thank You to qurawe For This Useful Post:
|
|
07-01-2011, 11:36 PM
|
#12
|
Newly Registered User
Join Date: May 2003
Location: Queensland
Posts: 332
Thanks: 20
Thanked 13 Times in 13 Posts
|
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.
|
|
|
The Following User Says Thank You to liddlem For This Useful Post:
|
|
07-02-2011, 09:42 AM
|
#13
|
Smeghead
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,829 Times in 1,579 Posts
|
Re: New Quick Tutorial - Exclude Data using a table
Quote:
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.
__________________
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
|
|
The Following User Says Thank You to boblarson For This Useful Post:
|
|
11-01-2011, 09:15 AM
|
#14
|
Nothing In Moderation
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,803
Thanks: 636
Thanked 337 Times in 307 Posts
|
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.
__________________
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!
|
|
|
The Following User Says Thank You to Rx_ For This Useful Post:
|
|
05-06-2012, 09:56 AM
|
#15
|
Newly Registered User
Join Date: May 2012
Posts: 4
Thanks: 0
Thanked 1 Time in 1 Post
|
Re: New Quick Tutorial - Exclude Data using a table
Thank you boblarson it's a very helpful tutorial.
|
|
|
The Following User Says Thank You to racolunga For This Useful Post:
|
|
Thread Tools |
|
Display Modes |
Rate This Thread |
Linear Mode
|
|
All times are GMT -8. The time now is 01:23 PM.
|
|