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

Dustin Stanley
Dustin Stanley used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Remote Training and Programming
Top Expert 2015
Commented:
hi Dustin,

Make a query showing the restrictions that you don't want, group by SkuID.  If SkuID is in there, you don't want it at all (if I understand correctly) ... so then ...

make another query with SKU table.
join in query that has the list of SKUs you don't want
join on SkuID
change join to show all records from SKUs and only records from the query above where there is a match
criteria under SkuID from the query with SKUs you don't want --> Is Null

there should get you just the SKUs that don't have any restrictions you don't want
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!
Distinguished Expert 2017
Commented:
Try following Crystal's directions again.  You need two queries.  The first selects all SKU's with restrictions you want to eliminate.  Group this query so you have only one record per SKU rather than one per restriction.

The second query joins the table to the query above.  You need to modify the join to be a Left (or Right) join depending on which table contains the entire file.  Then add criteria to the ID field from the query so that it looks for IDs that are null.  This will give you records from the main table where there is NO match in the query.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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 :)
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
Top Expert 2015

Commented:
I do not believe Dustin wants to delete records -- just exclude results ... so Dustin, don't do this!
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
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.
Top Expert 2016
Commented:
try this too

select SKUs.*
from SKUs LEFT join
(select SkuRestrictions.SkuID From SkuRestrictions where SkuRestrictionsID In(3,4,5,13,16,18)) AS S
On SKUs.SkuID=S.SkuID
where S.SkuID Is Null
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and Programming
Top Expert 2015
Commented:
Dustin, since Rey did not explain the SQL suggested ...
(select SkuRestrictions.SkuID From SkuRestrictions where SkuRestrictionsID In(3,4,5,13,16,18)) AS S
is a 'subquery' and referenced as "S" -- it can be used instead of making the first query, as both Pat and I suggested.  Until you are more comfortable with SQL, it is better, imo (in my opinion), to take baby steps

for the first query, you could modify what Rey gave as a subquery -- SQL would be something like:
select Distinct SkuRestrictions.SkuID From SkuRestrictions where SkuRestrictionsID In (3,4,5,13,16,18)

the 'Distinct' keyword ensures each SkuID will only be listed once.  This will prevent duplicate records from being reported from SKUs when the query/subquery is joined.  In this case, Distinct does the same thing as using GROUP BY since only one field is returned in the results.
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
Top Expert 2015

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial