Access Queries

Many thanks Ken, but the query doesn't return seasonal products for February for example + non seasonal products! I have got a list of 690 products!... which is not the answer I expect. I appreciate your help but it doesn't work anymore.

How are you determining what products are non-seasonal?  I see that the Products table includes an IsSeasonal column, but its value is FALSE in every row, so that can't be used.  My query is predicated on non-seasonal being where there are no matching rows in MonthAvailable, so if we count those products with the following query:

SELECT COUNT(*)

FROM Products

WHERE NOT EXISTS

    (SELECT *

     FROM MonthAvailable

     WHERE MonthAvailable.ProductID = Products.ProductID);

This returns a count of 646.

If we count the products which are available in the current month (February) with the following query:

SELECT COUNT(*)

FROM Products INNER JOIN MonthAvailable

ON MonthAvailable.ProductID = Products.ProductID

WHERE MonthAvailable = MONTH(DATE());

This returns 44.

So, adding the two gives us a total of 690 which is, as I'd expect, the number of rows returned by my first query.

The other possibility is that you are defining a product as non-seasonal where it has 12 matching rows in MonthAvailable, i.e. all months, which can be returned with this query:

SELECT Products.ProductID,ProductName

FROM Products INNER JOIN MonthAvailable

ON MonthAvailable.ProductID = Products.ProductID

GROUP BY Products.ProductID,ProductName

HAVING COUNT(*) = 12;

This returns 3 products only.   In this case the following query would return all non-seasonal products and those available in the current month:

SELECT 
DISTINCT Products.*
FROM Products INNER JOIN MonthAvailable ON Products.ProductID = MonthAvailable.ProductID
WHERE MonthAvailable = MONTH(DATE())
OR EXISTS 
    (SELECT MonthAvailable.ProductID 
     FROM MonthAvailable
     WHERE MonthAvailable.ProductID = Products.ProductID
     GROUP BY MonthAvailable.ProductID
     HAVING COUNT(*) = 12);

This returns 44 products.  However, this begs the question as to what is the seasonal/non-seasonal status of the large number of products with no matching rows in MonthAvailable?  In relational database terms the answer is NULL.  However, NULL is not a value, but the absence of a value so is semantically meaningless.

Hi Ken, thanks for your patience!

I may have misexpressed wrt IsSeasonal or not query.

You'll find in this link all the products that are seasonal month after month; as you imagine, some are present many months, such as "avocat", seasonable from January to April and from June to December. See:

https://www.mangerbouger.fr/manger-mieux/bien-manger-sans-se-ruiner/calendrier-de-saison

Even if you don't speak french, you'll understand.

As a consequence, all the other products not listed in the above link in my Products table are NOT seasonable, but my query must return them as well.

Hope this will help.

Enjoy your Sunday.

My French is a little rusty, but I had no difficulty reading the article.  What you are saying tallies with my original query, i.e. any product not represented in the MonthAvailable table is non-seasonal. However, to avoid any ambiguity arising from the Nulls I'm going to suggest another way of defining a non-seasonal product, which is to include one row for it in the MonthAvailable table with the ProductID of the product in the ProductID column and 0 in the MonthAvailable column.  The semantic ambiguity of a Null is thus removed, and the query can be simplified to a simple INNER JOIN as follows, which, if the data in MonthAvailable is comprehensive  for both seasonal and non-seasonal products, can be guaranteed to return the correct results for the current month:

SELECT Products.*

FROM Products INNER JOIN MonthAvailable ON Products.ProductID = MonthAvailable.ProductID

WHERE MonthAvailable = MONTH(DATE()) OR MonthAvailable = 0;

BTW, I realised after I sent my last reply that the final query in it did not need the subquery.  Any product for which the EXISTS predicate evaluates to TRUE would of course already be returned by the first criterion in the WHERE clause as the current month would be represented by a matching row in MonthAvailable, as there would be rows for all 12 months.  So, the following query would do the job:

SELECT 

DISTINCT Products.*

FROM Products INNER JOIN MonthAvailable ON Products.ProductID = MonthAvailable.ProductID

WHERE MonthAvailable = MONTH(DATE());

With the method I'm now suggesting for defining non-seasonal products that's now irrelevant, but it does illustrate how one has to be careful not to fixate on one issue.

Seems to be working properly, at least!

Many thanks Ken.

Best regards.