Update a table with results from a query (1 Viewer)

DampFloor · Nov 20, 2017

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 · Nov 20, 2017

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

Uncle Gizmo · Nov 20, 2017

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 · Nov 20, 2017

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 · Nov 24, 2017

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 · Nov 24, 2017

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 · Nov 24, 2017

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 · Nov 24, 2017

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 · Nov 24, 2017

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 · Nov 24, 2017

You're welcome - good luck.

Mark_ · Nov 27, 2017

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_ · Nov 27, 2017

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.

Update a table with results from a query (1 Viewer)

DampFloor

Registered User.

Attachments

MarkK

bit cruncher

Uncle Gizmo

Nifty Access Guy

The_Doc_Man

Immoderate Moderator

DampFloor

Registered User.

Attachments

JHB

Have been here a while

DampFloor

Registered User.

Attachments

JHB

Have been here a while

Attachments

DampFloor

Registered User.

JHB

Have been here a while

Mark_

Longboard on the internet

Mark_

Longboard on the internet

Similar threads

Users who are viewing this thread