Beginner question - building a database

[pmuel]

I am building my first database from scratch and I am just trying to learn the functions and how to make some relationships work. i was hoping someone could point me down a path. The example I am trying to work on now is having a list of products that uses multiple ingredients and how much of each ingredient that product uses. then have a database of all the ingredients and the price of each ingredient per unit. Can I build a table or query that if I have the list of ingredients and how much for the final product, can I get it to calculate the cost of the product based on the 2 or 3 tables? I am struggling to understand how to set up a table to have multiple fields that all reference the same raw materials table.

So I would have a raw materials list

1	Egg	$0.23	EA
2	Bacon	$0.54	oz
3	Cheese	$0.33	oz
4	Bread	$0.34	Slice
5	Salt	$0.12	oz

And then a products list

Meal	ingredient1	ingredient1 usage	ingrediant2	ingredient2 Usage	ingredient3	ingredient3 usage
Omelet	Egg	2	Bacon	2.5	Cheese	2

Then I can get a cost for the omelet because it knows that ingredient1 is eggs and that is the eggs from the raw material and can get the pricing per unit there and then do the same for ingredient2 and ingredient3


Let me know if that makes sense or if I am way off on what I am trying to do here. I am learning on my own and am the very start of this and not sure what the proper vocab words to help search for tutorials or anything.

[Micron]

That sort of db is one of the more difficult ones to start with. You could research recipe or bill of material types of db schemas. Here's a link to all sorts of schemas,
http://www.databaseanswers.org/data_...all_models.htm

plus those that I think any novice would be wise to study, the most important being db normalization.
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[davegri]

CupcakeDB-davegri-V01.zip

Here's a DB for a cupcake bakery. It's far too complicated for your specific requirements, but it does show how to maintain a pantry, keep pantry bulk prices, unit conversion to get ingredient qty price, recipe maintenance and so on.
It might be useful for ideas as you progress with your own design.

It does contain a large amount of sample data. If you'd like an empty DB for your testing, let me know.