Linking two table in Access

[dnjtdjq]

Hello,

Im an access beginner and struggling with linking and updating information within two tables.

So there is my table 1 called Ingredients table and I have ingredients ID, ingredients name and price for the first three colums.

I have another table called Recipe table and my columns consists of Recipe ID, ingredients and price.

What I want to do is, if I type ingredients name and price in Table 1, say "eggs" and "$6"

and in my Recipe table if I type eggs the price will be updated accordingly, in which case $6.

How can I do this?

 

[stumac]

Typically you would have 3 tables for this:

Tbl_Ingredients 'Used to store all your products
Tbl_Recipes 'Used to store all your recipe header info
Tbl_RecipIngredients 'Used to link the other 2 tables

You would not normally store the values in 2 tables, this is achieved by using queries to link the data. i.e.

Tbl_ingredients would have a product ID, description and unit cost.
Tbl_Recipe would have a recipe ID, description plus any other information that is relevant to the recipe (excluding ingredients)
Tbl_RecipeProducts would have recipe ID, Product ID, No. of units

The query you would create would then Pull the recipe details from the recipe table, the Ingredients details from the Ingredients table and the cost by multiplying the unit cost (from the Ingredients table) by the no of units from the RecipeIngredients table.

 

[Micron]

Nor is it good practice to store calculations. Suggest you read up on db normalization, for it sure reads like you are designing a db with spreadsheet paradigm. Spreadsheet data is stored in columns, databases in rows. To do otherwise invites a whole heap of trouble down the road.
Roger's Access Blog: What Is Normalization, Part I
and/or
Database Normalization Full Tutorial