hello, my name is Nabila. I have a small catering business.
right now I already build a database that contains:
table of menu
table of ingredients
menu - ingredients junction table (with the details of ingredients ratio for every menu)
table of supplier
ingredients - supplier junction table
production schedule for every menu
here is my problems:
I want to create a table that summarize my shopping list in a certain date, to a certain supplier… but I don’t know how can I make a table that automatically preview my ingredients need (what kind of ingredients, and how many that I need) in a certain date of menu production, based on my schedule of menu and my menu-ingredients junction table…
thank you in advance, :slightly_smiling_face: I wish you all the best
I am sorry, if the way I explain my problems is not clear enough… I still don’t understand how to solve my problem… :slightly_smiling_face: I really appreciate Your help… thank you very much
what I understand from your answer is: I have to manually input ingredients list and how many i need for a certain date and certain supplier, then I can roll it up in supplier table. am I right? :slightly_smiling_face: but if I do that, I cant keep the data from previous production, can I? Because I should only have the on going production every time.
I will try to attach some pics of my table, I hope this will give You a better understanding of my condition…
this is my table of menu schedule:
this is my recipe table
this is my menu table:
from there, I hope that I have another table that contains:
date of preparation
ingredients list that i should buy
supplier that i choose manually
actually I already have this table, that looks like:
The problem from the last table are:
the relation between ingredients and ratio, is not in a table form with single value. how can I make that data relationship to form a table, so that I can choose a different supplier for different ingredients manually. because ingredients and supplier are many to many relationship
From there, I think I can do a filter to show only the data from specific preparation date that I need…
As I saw you want to create in Table “Grocery Shopping Schedule” the process that you choose the supplier for every single order. In this table, every record is one order, right?
I guess that you have already linked the tables “Supplier” and “Ingredient” right? This link shows the supplier per ingredient. Now you need to add a new link between the “Supplier” and “Grocery Shopping Schedule” table. Using this column in “Grocery Shopping Schedule” table you will choose the Supplier you want to buy from.
But if every record in “Grocery Shopping Schedule” is one order then you will have to choose in one record all supplier for the whole order while and I think that may be not that user friendly. However in a first step just try this and if this is what you want we can see some data structure changes that will convert the “Grocery Shopping Schedule” in a more user friendly table.
Please do not hesitate to text back for the case :slightly_smiling_face: