Update a table with results from a query

[DampFloor]

Hello,

I am creating a database which identifies the cheapest supplier for each ingredient. I then need the identified cheapest price for each ingredient to populate a table which shows which ingredients are in which item. I created a query which can identify which vendor sells each ingredient the cheapest but cannot figure out how to get this data into the menu table. Attached are images of my min price query and menu list table

 

[MarkK]

It never makes sense in a database to have a item1, item2, item3, ..., itemN in the same row.
Mark

 

[Uncle Gizmo]

I agree with Mark..

See my blog for more info:-

http://www.niftyaccess.com/excel-in-access/

Sent from my SM-G925F using Tapatalk

 

[The_Doc_Man]

Not to be "piling on" but you need to take a quick review of normalization (regarding having fields like Ingredient1, Ingredient2, etc.) and either subqueries or JOIN queries as to how to bring the "cheapest source" query together with your main query.

Further, since I'll bet prices fluctuate, you don't want that information stored in the table. You want a query that shows you the cheapest sources today. And there is nothing wrong with the idea of basing a report or form on a query. And old recordset in a storm, as the old paraphrasing goes...

 

[DampFloor]

OK thanks for the help everyone. My apologies, this is my first time making a database.

i attached an image of the table and query i made which contains the menu ID and which ingredients are contained within that menu item. However i still cannot figure out how to get the lowest prices which i have found to be shown beside the the ingredients in a menu. Any help would be appreciated.

 

[JHB]

Where should the price come from?
Show some data from that table?
To get the lowest price use the function Min().
If you can't get it post your database, (zip it).

 

[DampFloor]

Hi JHB,

The price comes from a list of quotes given from suppliers.I did use the min function to return the lowest cost supplier for each ingredient. I have attached the database to this reply.

 

[JHB]

Open the query JHBFinalQuery.
You've an error in table Menu_Ingredient, the field Ingredient ID need to be Number type and not Text.

 

[DampFloor]

Thank you so much! This is a school project and there is a long way to go on it, so don't be suprised if you see more posts from me. Thank you again for the help, hopefully i can become more self sufficient with this

 

[JHB]

You're welcome - good luck.

 

[Mark_]

Please note, if this is a school assignment please specify when posting. The advice you get will be a bit different as we will know we need to explain more, though are less apt to give you exactly what needs to be done.

You will probably be given more references to "Here is how you do this in general with a detailed reason WHY" rather than "Min([YourField])" type answers.

 

[Mark_]

Please note, if this is a school assignment please specify when posting. The advice you get will be a bit different as we will know we need to explain more, though are less apt to give you exactly what needs to be done.

You will probably be given more references to "Here is how you do this in general with a detailed reason WHY" rather than "Min([YourField])" type answers.