We help IT Professionals succeed at work.

How can I set my Access Query up to filter out specific records

Dustin Stanley
on
120 Views
Last Modified: 2017-04-22
I have 2 tables.

SKUs
SkuRestrictions


Skus are products and SkuRestrictions are restrictions on a product.
A Sku can have multiple Sku Restrictions.

I need my query to remove the records of Skus with the SkuRestrictionsID of 3,4,5,13,16, or 18
Now my query does a good job of removing the exact records with these exact SkuRestrictionsID

BUT.....

Lets say a SkuID of 3381 has SkuRestrictionsID of 2 , 5 ,16 , and 20
The 5 and 16 will be removed but I need all of them removed from the results of SkuID 3381

How can I do this??? Thanks!
Comment
Watch Question

Remote Training and Programming
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

Commented:
Thank you Crystal for the help. I will try to explain this the best I can.

Lets say a SkuID of 3381 has SkuRestrictionsID of 2 and that same 3381 also has a SkuRestrictionsID of 5

and I want SkuRestrictionsID of 5 removed.   Then that means I want 3381 completely removed from the SKUs result query. NO 3381 at all NATTA! NUN!

With the way I am doing it. Which is what you said....
The results come back back with
SkuID| SkuRestrictionsID
---------------------------------------
3529| 10
3659|17
3381| 2               Get this out of here!

I understand it did as I said remove
SkuID| SkuRestrictionsID
---------------------------------------
3381| 5

But not 2!

I have to remove all records pertaining to 3381.

I hope this makes sense.  Think of it like this. If you had rules to go by and a product fell under one of the restrictions then that product isn't allow.  This is why each SKU has multiple SkuRestrictions because it can be restricted in several ways.

Like Used, Overweight, Food Etc...   So I have to figure out a way to remove it (3381) completely from my query if it falls under even 1 of them restrictions (SkuRestrictions)

Thank you!
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

Commented:
Thanks Pat. I just left work. I will be back in in about 8 hrs then I will try again.

I may have messed up. This part threw me off "criteria under SkuID from the query with SKUs you don't want --> Is Null"

I was thinking I wasn't supposed to put Is Null like you don't want this here.... I don't know :)
CERTIFIED EXPERT
Top Expert 2016

Commented:
try this query, create backup copy of table first
-this is the SQL statement of the query
-create a new query, go to SQL view of the query
-copy the SQL below and paste it to the new query

delete SKUs.*
from SKUs
where SKUs.SkuID In(select SkuRestrictions.SkuID From SkuRestrictions where SkuRestrictionsID In(3,4,5,13,16,18))
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
I do not believe Dustin wants to delete records -- just exclude results ... so Dustin, don't do this!
CERTIFIED EXPERT
Top Expert 2016

Commented:
then just use a select query


select SKUs.*
 from SKUs
 where SKUs.SkuID NOT In(select SkuRestrictions.SkuID From SkuRestrictions where SkuRestrictionsID In(3,4,5,13,16,18))
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
while a subquery can be used, it is good to teach beginners to use a saved query first so they can better understand the logic.  Dustin wants to learn, not just get a solution.  Also NOT IN is slow performance.

>> criteria under SkuID from the query with SKUs you don't want --> Is Null <<

the first query shows SKUs you don't want (the criteria for that is already in the saved query) ... the join is to show all records from SKUs so the records left will be the ones that are not listed in the query   -- resulting in the SKUs you want.  You can also, of course, join in other tables and get what you want from them.
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Dustin StanleyEntrepreneur

Author

Commented:
Thank you Crystal for all the details. These details help a ton in the learning process.
Dustin wants to learn, not just get a solution.
Amen to that. I review all these questions I ask later for future problems and this helps a ton!

Pat
Then add criteria to the ID field from the query so that it looks for IDs that are null.
That qoute really cleared it for me and also the extra explanation. Sometimes you can say the same thing but in a slightly different way and it will clear things up. I understand that. I have trouble explaining things.

Rey

I absolutely LOVE the straight to the point BAM! get it done answers. Awesome!  But yes I am trying to learn and understand it so I can fix future problems but hey keep them answers coming even if I have to pick the code apart myself!


If it wasn't for you guys and gal then I have not the clue what wall I would be banging my head on!


Thanks for all the help it means a ton!
Dustin StanleyEntrepreneur

Author

Commented:
THANKS!
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
CERTIFIED EXPERT
Top Expert 2015

Commented:
you're welcome, Dustin ~ happy to help