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!
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!
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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!
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.
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.
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.
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
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
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.
(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.
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!
Premium Content
You need an Expert Office subscription to comment.Start Free Trial