By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,799 Members | 1,362 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,799 IT Pros & Developers. It's quick & easy.

How do I exclude certain data from my query?

P: 2
I have an Access 2016 table with several columns, one of which is a column SerialNumberExclude. I want to compare data from another table and if the serial number in this table matches any serial number in the SerialNumberExclude, I want to exclude those records in my query. hope that's not too confusing. I'm only YouTube University qualified with Access! LOL

**Additional info: Serial numbers are a text format and are entered in the column like this: 123, 1234, 345, 3456, etc. There can be multiple serial numbers associated with an item in my table. So if serial number 345 is in my table that I am comparing, I don't want to see it in my query.
Feb 18 '20 #1
Share this Question
Share on Google+
6 Replies


twinnyfo
Expert Mod 2.5K+
P: 3,387
Soupy,

Welcome to Bytes!

Example:

Expand|Select|Wrap|Line Numbers
  1. tblSoupySales
  2. SoupyID
  3. SoupyName
  4. SoupyPlace
  5. SerialNumberExclude
Expand|Select|Wrap|Line Numbers
  1. tblSoupySerials
  2. SerialNumber
  3. SoupyBreakfast
  4. SoupyLunch
  5. SoupyDinner
Your Query would look something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM tblSoupySales 
  3. LEFT JOIN tblSoupySerials 
  4. ON tblSoupySale.SerialNumberExclude = tblSoupySerials.SerialNumber 
  5. WHERE tblSoupySerials.SerialNumber Is Null;
This should display all records in tblSoupySales that do NOT have a matching record in tblSoupySerials.

Hope this hepps!
Feb 18 '20 #2

P: 2
Thanks for the reply. I guess I'm not understanding as this is new to me. Can this be done with a Criteria statement in the Access query? Where the query compares the two tables and returns all line items except those where the serial number is a certain number? Sorry for my ignorance on this.
Feb 18 '20 #3

twinnyfo
Expert Mod 2.5K+
P: 3,387
Soupy,

Yes and yes. You can build this query in the Query Designer or simply type it in in the SQL view.

If you are designing the query in the Query Designer, just add your two tables and create a relationship between the two tables. Double click the relationship line to edit it and select "Include all records from tblSoupySales ...." Then drag tblSoupySerials.SerialNumber to the fields list, and add "Is Null" in the criteria section.

This is a very common design. Often, you only want the records that have a matching record in the second table, but it is often helpful to know which records do not have corresponding records.

Again, hope this hepps!
Feb 18 '20 #4

NeoPa
Expert Mod 15k+
P: 31,662
I hate to rain on anyone's parade here, but if you really mean you are entering multiple serials to exclude into a string value then trying to exclude any and all of those items then this is far more complicated than shown so far.

The short response is to avoid such a design like the plague, but that's another story.
Feb 25 '20 #5

100+
P: 111
twinnyfo wrote
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM tblSoupySales 
  3. LEFT JOIN tblSoupySerials 
  4. ON tblSoupySale.SerialNumberExclude = tblSoupySerials.SerialNumber 
  5. WHERE tblSoupySerials.SerialNumber Is Null;
Let's see this Query in action.
Expand|Select|Wrap|Line Numbers
  1. tblSoupySales
  2. +-------+---------+----------+-------------------+
  3. |SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|
  4. +-------+---------+----------+-------------------+
  5. |     1 |      A |        s |                 1 |
  6. |     2 |       B |        t |                 2 |
  7. |     3 |       C |        u |                 1 |
  8. |     4 |       D |        v |                 1 |
  9. |     5 |       E |        w |                 1 |
  10. |     6 |       F |        x |                 1 |
  11. |     7 |       G |        y |                 2 |
  12. |     8 |       H |        z |                 2 |
  13. +-------+---------+----------+-------------------+
  14.  
  15. tblSoupySerials
  16. +------------+--------------+----------+-----------+
  17. |SerialNumber|SoupyBreakfast|SoupyLunch|SoupyDinner|
  18. +------------+--------------+----------+-----------+
  19. |         1  |            y |        y |         y | 
  20. |         2  |            n |        y |         y |
  21. |         3  |            y |        y |         n |
  22. |         4  |            n |        n |         n |
  23. |         5  |            y |        n |         y |
  24. |         6  |            n |        n |         y |
  25. +------------+--------------+----------+-----------+
  26.  
  27. Join result(Without "Where" statement)
  28. +-------+---------+----------+-------------------+------------+--------------+----------+-----------+
  29. |SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|SerialNumber|SoupyBreakfast|SoupyLunch|SoupyDinner|
  30. +-------+---------+----------+-------------------+------------+--------------+----------+-----------+
  31. |     1 |      A |        s |                 1 |         1  |            y |        y |         y |
  32. |     2 |       B |        t |                 2 |         2  |            n |        y |         y |
  33. |     3 |       C |        u |                 1 |         1  |            y |        y |         y |
  34. |     4 |       D |        v |                 1 |         1  |            y |        y |         y |
  35. |     5 |       E |        w |                 1 |         1  |            y |        y |         y |
  36. |     6 |       F |        x |                 1 |         1  |            y |        y |         y |
  37. |     7 |       G |        y |                 2 |         2  |            n |        y |         y |
  38. |     8 |       H |        z |                 2 |         2  |            n |        y |         y |
  39. +-------+---------+----------+-------------------+------------+--------------+----------+-----------+
  40.  
  41. Query execute result
  42. +-------+---------+----------+-------------------+------------+--------------+----------+-----------+
  43. |SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|SerialNumber|SoupyBreakfast|SoupyLunch|SoupyDinner|
  44. +-------+---------+----------+-------------------+------------+--------------+----------+-----------+
  45. +-------+---------+----------+-------------------+------------+--------------+----------+-----------+
  46.  
Result hit is 0 record.
The result it actually want is
Expand|Select|Wrap|Line Numbers
  1. +-------+---------+----------+-------------------+
  2. |SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|
  3. +-------+---------+----------+-------------------+
  4. |     3 |       C |        u |                 1 |
  5. |     4 |       D |        v |                 1 |
  6. |     5 |       E |        w |                 1 |
  7. |     6 |       F |        x |                 1 |
  8. |     7 |       G |        y |                 2 |
  9. |     8 |       H |        z |                 2 |
  10. +-------+---------+----------+-------------------+
  11.  
Fixed the query by modified the table structure.
Expand|Select|Wrap|Line Numbers
  1. tblSoupySales
  2. +-------+---------+----------+------------+
  3. |SoupyID|SoupyName|SoupyPlace|SerialNumber|
  4. +-------+---------+----------+------------+
  5. |     1 |      A |        s |         10 |
  6. |     2 |       B |        t |         20 |
  7. |     3 |       C |        u |         30 |
  8. |     4 |       D |        v |         40 |
  9. |     5 |       E |        w |         50 |
  10. |     6 |       F |        x |         60 |
  11. |     7 |       G |        y |         70 |
  12. |     8 |       H |        z |         80 |
  13. +-------+---------+----------+------------+
  14.  
  15. tblSoupySerials
  16. +-------------------+--------------+----------+-----------+
  17. |SerialNumberExclude|SoupyBreakfast|SoupyLunch|SoupyDinner|
  18. +-------------------+--------------+----------+-----------+
  19. |                10 |            y |        y |         y |
  20. |                20 |            n |        y |         y |
  21. +-------------------+--------------+----------+-----------+
  22.  
New Query is
Expand|Select|Wrap|Line Numbers
  1. SELECT * 
  2. FROM tblSoupySales as a 
  3. LEFT JOIN tblSoupySerials as b 
  4. ON (a.SerialNumber = b.SerialNumberExclude) 
  5. WHERE b.SerialNumberExclude IS NULL;
  6.  
Expand|Select|Wrap|Line Numbers
  1. Join result(Without "Where" statement)
  2. +-------+---------+----------+------------+-------------------+--------------+----------+-----------+
  3. |SoupyID|SoupyName|SoupyPlace|SerialNumber|SerialNumberExclude|SoupyBreakfast|SoupyLunch|SoupyDinner|
  4. +-------+---------+----------+------------+-------------------+--------------+----------+-----------+
  5. |     1 |      A |        s |         10 |                10 |            y |        y |         y |
  6. |     2 |       B |        t |         20 |                20 |            n |        y |         y |
  7. |     3 |       C |        u |         30 |              NULL |         NULL |     NULL |      NULL |
  8. |     4 |       D |        v |         40 |              NULL |         NULL |     NULL |      NULL |
  9. |     5 |       E |        w |         50 |              NULL |         NULL |     NULL |      NULL |
  10. |     6 |       F |        x |         60 |              NULL |         NULL |     NULL |      NULL |
  11. |     7 |       G |        y |         70 |              NULL |         NULL |     NULL |      NULL |
  12. |     8 |       H |        z |         80 |              NULL |         NULL |     NULL |      NULL |
  13. +-------+---------+----------+------------+-------------------+--------------+----------+-----------+
  14.  
  15. Query execute result
  16. +-------+---------+----------+------------+
  17. |SoupyID|SoupyName|SoupyPlace|SerialNumber|
  18. |     3 |       C |        u |         30 |
  19. |     4 |       D |        v |         40 |
  20. |     5 |       E |        w |         50 |
  21. |     6 |       F |        x |         60 |
  22. |     7 |       G |        y |         70 |
  23. |     8 |       H |        z |         80 |
  24. +-------+---------+----------+------------+
  25.  
4 Weeks Ago #6

twinnyfo
Expert Mod 2.5K+
P: 3,387
Sio,

Thanks for the discussion. However, OP states:
I want to compare data from another table and if the serial number in this table matches any serial number in the SerialNumberExclude, I want to exclude those records in my query.
Based upon this definition, and based upon the data in your tables above, my query would, indeed, return no records. This is because every record in tblSoupySales has a corresponding record in tblSoupySerial. However, when you change any of the values in the Field SerialNumberExclude to a value that does NOT exist in tblSoupySerials, that record will show up.

I believe this this the result the OP wants, but outside of OP confirming or denying, we must go on mere interpretation of their post.
4 Weeks Ago #7

Post your reply

Sign in to post your reply or Sign up for a free account.