How to select and also exclude criteria from same field

[ST99]

Hi there, I am fairly new to Access therefore I hope you can help me figure this out. Listed below are products and suppliers. If I wanted to filter on a product e.g. Lemonade and want to see a list of suppliers BUT also exclude suppliers that sell Tea from the list. The answer should be "Sweet Drinks" as the other two are Tea suppliers too. But do I get to this answer from the data below?

What type of join would I need please? And how do I include a criteria and exclude others at the same time? I am hoping to connect the query to Excel and use a pivot table to select a product while excluding rows of data connected to the excluded product.

Many Thanks!!

Product	SUP_ID	Supplier	Profit £ (Mill)
Coffee	S01	AA beverages	1.1​
Coffee	S02	Drinks R Us	0.9​
Hot Chocolate	S01	AA beverages	1.1​
Hot Chocolate	S03	Sweet Drinks	0.5​
Lemonade	S01	AA beverages	1.1​
Lemonade	S02	Drinks R Us	0.9​
Lemonade	S03	Sweet Drinks	0.5​
Tea	S01	AA beverages	1.1​
Tea	S02	Drinks R Us	0.9​

 

[plog]

You need a subquery. That subquery would identify everyone who sells Tea. You then bring that query into your existing query and LEFT JOIN (show all from original query source, just those matching for the Tea query). on Supplier field (or SUP_ID if it is the id of the supplier). In the criteria, you put 'Lemonade' under the the product field and IS NULL under the supplier field from the Tea query.

That will produce the results you want. Give it a shot and if it doesn't, post your SQL so we can debug.

 

[The_Doc_Man]

Below is ONE POSSIBLE example of how you might do this. The overall method of having a query within a query like that is called a SUBQUERY, in case you want to do any online lookups of that method for learning purposes.

SELECT M1.Supplier, M1.SUP_ID FROM mytable M1
WHERE M1.Product = "Lemonade" AND M1.SUP_ID NOT IN 
    (SELECT M2.SUP_ID FROM mytable M2 WHERE M2.Product = "Tea") ;

The M1 and M2 are because you are looking at the same table for two different factors (main query and subquery) so you have to help Access decide which factor goes with which query. It is called an "Alias" when you assign an internal name like that.

And by the way, that was your first post. Welcome to the forums.

 

[gemma-the-husky]

That is quite an awkward query. Straightforward enough with experience but it's something you need to experiment with to find a good solution, so that you can find flexible ways to get results yourself.

 

[ebs17]

SELECT
   M1.Supplier,
   M1.SUP_ID
FROM
   mytable AS M1
WHERE
   M1.Product = "Lemonade"
      AND
   NOT EXISTS
      (
         SELECT
            NULL
         FROM
            mytable AS M2
         WHERE
            M2.SUP_ID = M1.SUP_ID
               AND
            M2.Product = "Tea"
      )

Or

SELECT
   M1.Supplier,
   M1.SUP_ID
FROM
   mytable AS M1
      LEFT JOIN
         (
            SELECT
               SUP_ID
            FROM
               mytable
            WHERE
               Product = "Tea"
         ) AS M2
         ON M1.SUP_ID = M2.SUP_ID
WHERE
   M1.Product = "Lemonade"
      AND
   M2.SUP_ID IS NULL

The NOT IN construct that @The_Doc_Man shows is functionally correct, but has no index support and therefore higher performance problems.

 

[theDBguy]

Hi. Welcome to AWF!

As you can see, we can do this in Access. I am not sure though how you will do it Excel using a pivot table to supply the criteria.

 

[ebs17]

I am hoping to connect the query to Excel and use a pivot table to select a product while excluding rows of data connected to the excluded product.

You can also create a pivot table = crosstab query directly in Access. One of the above queries saved as Qx:

TRANSFORM
   MIN(M.Profit) AS X
SELECT
   M.Supplier,
   M.SUP_ID
FROM
   mytable AS M
      INNER JOIN Qx
      ON M.SUP_ID = Qx.SUP_ID
GROUP BY
   M.Supplier,
   M.SUP_ID
PIVOT
   M.Product

Hard-coded values such as “Tea” or “Lemonade” would be replaced with parameters and thus introduced flexibly.