Avatar of Dustin Stanley
Dustin Stanley
 asked on

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

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!
Microsoft AccessVBA

Avatar of undefined
Last Comment
crystal (strive4peace) - Microsoft MVP, Access

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dustin Stanley

ASKER
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!
SOLUTION
PatHartman

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dustin Stanley

ASKER
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 :)
Rey Obrero (Capricorn1)

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))
Your help has saved me hundreds of hours of internet surfing.
fblack61
crystal (strive4peace) - Microsoft MVP, Access

I do not believe Dustin wants to delete records -- just exclude results ... so Dustin, don't do this!
Rey Obrero (Capricorn1)

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, Access

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Rey Obrero (Capricorn1)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
crystal (strive4peace) - Microsoft MVP, Access

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dustin Stanley

ASKER
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 Stanley

ASKER
THANKS!
crystal (strive4peace) - Microsoft MVP, Access

you're welcome, Dustin ~ happy to help
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck