Access Queries

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.

Thank you for your help, but I am a totally newbee on Access. I built this database in following ChatGPT, but obviously it was not the right way to do it.

So, sorry to not understand all your comments. I will try to better explain in details later on what I am waiting from this database.

Regards,

CM

Thank you for your help, but I am a totally newbee on Access. I built this database in following ChatGPT, but obviously it was not the right way to do it.

So, sorry to not understand all your comments. I will try to better explain in details later on what I am waiting from this database.

Regards,

CM

I wouldn't do this in a query. You should have a form where you select items to be bought. On that form, you should have a combobox where you select the Product. the Rowsource of that combo should look like this:

SELECT ProductID, ProductName, IsSeasonal, Price

FROM Products

ORDER BY ProductName;

Notice I have changed the field names. You shouldn't use the default ID name for your autonumber keys. Also Name is a reserved word in Access and shouldn't be used as an object name.

You can set the combobox to display the IsSeasonal field when the combobox is dropped down so you can see if it is or not.

One other recommendation, You should include the Price in your ShoppingList table since prices change and you may want to capture the price at the time of shopping.

Show us one of those queries that didn't achieve your desired result. That is more helpful than simply saying it didn't work.

For example, this query should show the results you require.

SELECT Products.ID, Products.Name, Products.IsSeasonal, ShoppingList.N°, ShoppingList.QuantityToBuy

FROM Products INNER JOIN ShoppingList

ON Products.ID = ShoppingList.ProductID  

Also, it would be helpful to explain where and how you want to use the query.

On a different topic. this is an unlikely choice for a field name in a table, N° and really should be changed.

In the Products and Recupes tables, the same is true of the field, Name. That should be ProductName, or RecipeName, or something similar. "Name" is is a reserved word in Access and should never be used for field names in user tables.