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!
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!
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!
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!
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 :)
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 :)
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))
-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))
I do not believe Dustin wants to delete records -- just exclude results ... so Dustin, don't do this!
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))
select SKUs.*
from SKUs
where SKUs.SkuID NOT In(select SkuRestrictions.SkuID From SkuRestrictions where SkuRestrictionsID In(3,4,5,13,16,18))
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.
>> 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.
Thank you Crystal for all the details. These details help a ton in the learning process.
Pat
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 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!