Hi,
I need some help with designing my data base and then implementing it. I most probably won't know how to implement your suggestion so would appreciate an implementation hint following your design suggestion.
Although I am not putting this question in much technical words, I assure you I can follow up your instructions if you give me a clue and your time wont' be wasted! thank you.
Here is what I have got so fat:
tables, fields and relationships:
Customers: (John, Edgard, Calvin) (relationship: each customer has many orders)
Products: (Blue, Green, Yellow) (relationship: each product has many order details)
Orders: (a field that looks up customers form the customers table, date field,etc.) (relationship: each order has many order detials)
OrderDetails: (a look up field that looks up products from the Products table and an "Order weight" field. An example record will be "5 Kg of Blue product")
and I have designed the necessary forms and sub-forms to enter orders and order details data.
What I need help with:
the final output of the system will be a one day "Production Plan" like this:
2 Kg of (John's 5 Kg order of Blue Product)
3 Kg of (Edgard's 10 Kg order of Yellow Product)
5 Kg of (John's 5 Kg order of Yellow Product)
10 Kg of (Edgard's 15 Kg order of Green Product)
what I have in mind is that I will need a "Production Plan" table which its fields shall include "date", "Production Plan number", Etc.
and a "Production Plan details" table. An example of its records shall be similar to above blue lines in bold (in a table format of course. the expressions in the parentheses can be replaced with a the corresponding key ID of the "order details" table)
Here are some more details to consider:
- Each day, the operator will use the list of orders waiting for production to produce a "Production Plan" and send it to the workshop.
- the following day, the waiting list should be updated assuming the previous day's "production Plan" has been accomplished and new orders have arrived.
- an order detail record (John's 5 Kg order of Blue Product) might be produced in several days.
- the most important point for me is that, for the convenience of the operator, It will be best if the operator can see the whole "waiting list" ordered by the products and fill in the "production weight" for each record in the same window (according to the criteria he has in mind such as customer and order priority, production line capacity, etc). Maybe the "waiting list" can be duplicated as the "Production Plan" and the operator can fill in a field determining the "Production weight" for each record. In other words, I don't want the operator to chose the values for the "Production plan details" from a drop down looking up the values from the "waiting list enquiry".
I hope I have been able to convey what I have in mind.
again thank you for your time. I appreciate it in advance.