How do I exclude certain data from my query?

[Soupy]

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.

[twinnyfo]

Soupy,

Welcome to Bytes!

Example:

tblSoupySales
SoupyID
SoupyName
SoupyPlace
SerialNumberExclude

tblSoupySerials
SerialNumber
SoupyBreakfast
SoupyLunch
SoupyDinner

Your Query would look something like:

SELECT * 
FROM tblSoupySales 
LEFT JOIN tblSoupySerials 
ON tblSoupySale.SerialNumberExclude = tblSoupySerials.SerialNumber 
WHERE tblSoupySerials.SerialNumber Is Null;

This should display all records in tblSoupySales that do NOT have a matching record in tblSoupySerials.

Hope this hepps!

[Soupy]

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.

[twinnyfo]

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!

[NeoPa]

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.

[SioSio]

twinnyfo wrote

SELECT * 
FROM tblSoupySales 
LEFT JOIN tblSoupySerials 
ON tblSoupySale.SerialNumberExclude = tblSoupySerials.SerialNumber 
WHERE tblSoupySerials.SerialNumber Is Null;

Let's see this Query in action.

tblSoupySales
+-------+---------+----------+-------------------+
|SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|
+-------+---------+----------+-------------------+
|     1 |   　  A |        s |                 1 |
|     2 |       B |        t |                 2 |
|     3 |       C |        u |                 1 |
|     4 |       D |        v |                 1 |
|     5 |       E |        w |                 1 |
|     6 |       F |        x |                 1 |
|     7 |       G |        y |                 2 |
|     8 |       H |        z |                 2 |
+-------+---------+----------+-------------------+
 
tblSoupySerials
+------------+--------------+----------+-----------+
|SerialNumber|SoupyBreakfast|SoupyLunch|SoupyDinner|
+------------+--------------+----------+-----------+
|         1  |            y |        y |         y | 
|         2  |            n |        y |         y |
|         3  |            y |        y |         n |
|         4  |            n |        n |         n |
|         5  |            y |        n |         y |
|         6  |            n |        n |         y |
+------------+--------------+----------+-----------+
 
Join result(Without "Where" statement)
+-------+---------+----------+-------------------+------------+--------------+----------+-----------+
|SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|SerialNumber|SoupyBreakfast|SoupyLunch|SoupyDinner|
+-------+---------+----------+-------------------+------------+--------------+----------+-----------+
|     1 |   　  A |        s |                 1 |         1  |            y |        y |         y |
|     2 |       B |        t |                 2 |         2  |            n |        y |         y |
|     3 |       C |        u |                 1 |         1  |            y |        y |         y |
|     4 |       D |        v |                 1 |         1  |            y |        y |         y |
|     5 |       E |        w |                 1 |         1  |            y |        y |         y |
|     6 |       F |        x |                 1 |         1  |            y |        y |         y |
|     7 |       G |        y |                 2 |         2  |            n |        y |         y |
|     8 |       H |        z |                 2 |         2  |            n |        y |         y |
+-------+---------+----------+-------------------+------------+--------------+----------+-----------+
 
Query execute result
+-------+---------+----------+-------------------+------------+--------------+----------+-----------+
|SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|SerialNumber|SoupyBreakfast|SoupyLunch|SoupyDinner|
+-------+---------+----------+-------------------+------------+--------------+----------+-----------+
+-------+---------+----------+-------------------+------------+--------------+----------+-----------+
 

Result hit is 0 record.
The result it actually want is

+-------+---------+----------+-------------------+
|SoupyID|SoupyName|SoupyPlace|SerialNumberExclude|
+-------+---------+----------+-------------------+
|     3 |       C |        u |                 1 |
|     4 |       D |        v |                 1 |
|     5 |       E |        w |                 1 |
|     6 |       F |        x |                 1 |
|     7 |       G |        y |                 2 |
|     8 |       H |        z |                 2 |
+-------+---------+----------+-------------------+
 

Fixed the query by modified the table structure.

tblSoupySales
+-------+---------+----------+------------+
|SoupyID|SoupyName|SoupyPlace|SerialNumber|
+-------+---------+----------+------------+
|     1 |   　  A |        s |         10 |
|     2 |       B |        t |         20 |
|     3 |       C |        u |         30 |
|     4 |       D |        v |         40 |
|     5 |       E |        w |         50 |
|     6 |       F |        x |         60 |
|     7 |       G |        y |         70 |
|     8 |       H |        z |         80 |
+-------+---------+----------+------------+
 
tblSoupySerials
+-------------------+--------------+----------+-----------+
|SerialNumberExclude|SoupyBreakfast|SoupyLunch|SoupyDinner|
+-------------------+--------------+----------+-----------+
|                10 |            y |        y |         y |
|                20 |            n |        y |         y |
+-------------------+--------------+----------+-----------+
 

New Query is

SELECT * 
FROM tblSoupySales as a 
LEFT JOIN tblSoupySerials as b 
ON (a.SerialNumber = b.SerialNumberExclude) 
WHERE b.SerialNumberExclude IS NULL;
 

Join result(Without "Where" statement)
+-------+---------+----------+------------+-------------------+--------------+----------+-----------+
|SoupyID|SoupyName|SoupyPlace|SerialNumber|SerialNumberExclude|SoupyBreakfast|SoupyLunch|SoupyDinner|
+-------+---------+----------+------------+-------------------+--------------+----------+-----------+
|     1 |   　  A |        s |         10 |                10 |            y |        y |         y |
|     2 |       B |        t |         20 |                20 |            n |        y |         y |
|     3 |       C |        u |         30 |              NULL |         NULL |     NULL |      NULL |
|     4 |       D |        v |         40 |              NULL |         NULL |     NULL |      NULL |
|     5 |       E |        w |         50 |              NULL |         NULL |     NULL |      NULL |
|     6 |       F |        x |         60 |              NULL |         NULL |     NULL |      NULL |
|     7 |       G |        y |         70 |              NULL |         NULL |     NULL |      NULL |
|     8 |       H |        z |         80 |              NULL |         NULL |     NULL |      NULL |
+-------+---------+----------+------------+-------------------+--------------+----------+-----------+
 
Query execute result
+-------+---------+----------+------------+
|SoupyID|SoupyName|SoupyPlace|SerialNumber|
|     3 |       C |        u |         30 |
|     4 |       D |        v |         40 |
|     5 |       E |        w |         50 |
|     6 |       F |        x |         60 |
|     7 |       G |        y |         70 |
|     8 |       H |        z |         80 |
+-------+---------+----------+------------+
 

[twinnyfo]

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.

[NeoPa]

I'd just like to repeat here that the best advice we can give to this OP is that they should be approaching this completely differently.

DO NOT use lists within a string and expect a database system to work well with it.
DO NOT use variable-length strings in your PK if it's at all possible to avoid.

They have explained they're new and mistakes are easily made. Unless we, as experts, explain this to them clearly they may go off without that most important understanding.

[lewish95]

The EXCEPT operator is used to exclude like rows that are found in one query but not another. It returns rows that are unique to one result. To use the EXCEPT operator, both queries must return the same number of columns and those columns must be of compatible data types.