Expanding on the 'bakers database' design

[Albe]

I did what I could to find a previous question that answers mine but the closest I could come was this bakery example which does a good job getting me almost there.

www .access-programmers.co.uk/forums/showthread.php?t=108048
(sorry about the link format)

The hypothetical in the above post describes how a baker might set up a table of ingredients and a table of cakes that they sell.

This is just what I want to do but where I'm stuck is the next step where I want to track lot numbers of the ingredients for each order.

I have customer A who orders a cake that I call 'Simple cake' and it uses flour, sugar, and eggs to make this cake. I want to be able to make a new entry in another table where I can store the order details such as date I made the cake, lot number of the flour/sugar/eggs, expiration date of the cake and so on.

I think I could manage that except the confusing part for me is when I go to make my next order for customer B they order a 'complicated cake' which uses flour, sugar, eggs, milk, nuts, frosting, sprinkles, birthday candles. How can these two different types of cakes fit into my above described order table where I track the ingredient lot numbers along with the other information?

 

[spikepl]

Show your data structure - take screenshot of relations window with all tables expanded fully, and you may have to zip the screenshots to attach them here

 

[Albe]

Here is what I have. I have solvents that I call 'prepped reagents' and those are listed in the PreppedReagents table. There I define the name of each one. Next in order to make these prepped reagents I need to use stock reagents listed in the Reagents table. These are the ingredients used to make the prepped reagents each reagent is unique. Lastly I made the IngredientAmount table where I made a record for each item of each prepped reagent and defined the amount. The two fields in this table come from the other two tables.

I don't know what direction I need to go in to make what I described above. I want to take the ingredients and amount listed under each prepped reagent and make a table that stores a running list of reagents made. For example today if I made the MeOH:EtOAc I need to store the lot number of the stock MeOH and EtOAc and the date. (also not present in this example some items take 2 stock reagents and others take 5 or more to make)