Access Queries

Yes please tell me how to implement that solution, as I am just using Access for days and obviously don't know much!

Your question was, "But every month, I'll have to select manually every seasonal product."

I wasn't asking which table that is in, I was asking for the rules about selecting seasonal products. What does that process involve? Are you manually checking them to indicating that they are seasonal? Are you selecting only seasonal products for some use?

Scott's description of a form and controls is appropriate for selecting products which have been designated a seasonal. What we need to understand is the business purpose of "Selecting products".

Yes please tell me how to implement that solution, as I am just using Access for days and obviously don't know much!

Again, did you reread my initial response? Do you have any SPECIFIC questions about how to implement it.

Rather than having IsSeasonal and IsAvailableThisMonth columns in the Products table create the following related table:

MonthsAvailable

….ProductID (FK – Long Integer Number)

….MonthAvailable  (Integer Number)

Create an enforced relationship between Products and this table on ProductID. The primary key of this table is a composite of the two columns.  For each seasonal product this table would include one or more rows with values for the months in which the product is available.  The following query would return all seasonal products available in the current month:

SELECT *

FROM Products INNER JOIN MonthsAvailable

ON Products.ProductID = MonthsAvailable.ProductID

WHERE MonthAvailable = MONTH(DATE());

If you want to return all seasonal products available in the current month, and all non-seasonal products, i.e. those with no matching rows in MonthAvailable:

SELECT *

FROM Products INNER JOIN MonthsAvailable

ON Products.ProductID = MonthsAvailable.ProductID

WHERE MonthAvailable = MONTH(DATE()) OR MonthAvailable IS NULL;

The above is one of the few situations in which a query can be restricted on a column on the right side of a LEFT OUTER JOIN.  Alternatively for non-seasonal products you could include 12 rows for each in MonthsAvailable, with values from 1 to 12, and use the first query.

What does that process involve? Are you manually checking them to indicating that they are seasonal? Are you selecting only seasonal products for some use?

What I want each month is to have a request where all the seasonal products pop up in order to cook accordingly with the products of season. I.e in January, I know all these products but I don't know how to get them automatically when I choose a specific month. Not easy to explain as I don't know Access very well! Do you neeed anything else to further understand?