Access Queries

Anonymous
2025-01-27T08:46:50+00:00

Morning, I have got a problem with Queries on my computer running Windows 11.

I explain: I would like that, every time, when I want to add products on my list to be bought, the query shows me if this or these products are seaseonable.

I tried many times but unsuccessfully! Please see attached relations between tables.

I don't know SQL at all.
Many thanks for your help.
Regards,

CM

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

45 answers

Sort by: Newest
Most helpful Newest Oldest
  1. George Hepworth 23,120 Reputation points Volunteer Moderator
    2025-01-27T18:40:28+00:00

    I based the sample SQL on your screenshot of your tables. It should be really straightforward to create.

    Now you can save this query and run it. Tell us what the results are.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2025-01-27T16:05:33+00:00

    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

    Was this answer helpful?

    0 comments
  3. Anonymous
    2025-01-27T16:05:16+00:00

    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

    Was this answer helpful?

    0 comments
  4. ScottGem 68,840 Reputation points Volunteer Moderator
    2025-01-27T13:41:42+00:00

    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.

    Was this answer helpful?

    0 comments
  5. George Hepworth 23,120 Reputation points Volunteer Moderator
    2025-01-27T12:44:22+00:00

    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.

    Was this answer helpful?

    0 comments