How to split an order in a report or Form (1 Viewer)

Hi Mike

MajP is 100% correct in saying you need to normalise your table.

Hey everyone,



I should have clarified what i meant by All products scheduled.



The company That is interested in this Manufactures vitamins. They need a way to track when customer orders are scheduled to manufacture.

Every Customer Order has one or more items that they want us to make for them. Sometimes we call these items Products or Customer Products. We also like to refer to them as "Finished Goods" because every customer Item/Product requires a specific amount of one or more Raw-Materials/Goods. When the Raw Materials are measured and processed into a desired customer item/product we get one Finished Good. Customers refer to them as Bottles or Units .


I believe the confusion is coming from the schedule name. I think "Product Manufacturing Schedule" would be more precise to what this schedule is used for.



in my earlier question, what i meant was to see all "In Process" Customer Products aka Customer items scheduled. So that means every product for all customers that have placed an order.



"in Process" is a production status label. If a product is "In Process" then it can be scheduled for production because there is a sufficient amount of raw materials to produce it. If a Product/Item does not have enough raw goods to produce, then we label its production status according to what is missing.



Here is a list of the other the different production statuses,



tblStatusType STATUS IN PROCESS (cleared to start, Manufacturing cycle initiated)
BOTTLES/CLS - MSSING (Cannot schedule for production) LABELS - MISSING (Cannot schedule for production) NECKBANDS - MISSING (Cannot schedule for production)
POWDERS - MISSING (Cannot schedule for production) READY TO SHIP (QC passed Clear to ship)
FINISHED (Product shipped removed from Order Mngt "Active Orders list")

Mark_



Yes we have deifferent production lines but a manufacturing hold could happen anywhere in the process for any reason.



we have a few different steps in the manufacturing process,

1. Blending operation
2. Capsule filling operation

3. Drink filling op.
4. tablet press op.

5. Bottling and packaging Op. ( this step is what the schedule is referencing to)



The schedule i am making is "phase one" and its just for Bottling. right now the production manager has to schedule the bottling process according to the speed a customer's Item/Product moves through the other manufacturing steps.

In "phase two" i will incorporate the Blending Op. schedule but that would be after i learn how to make a decent Phase one schedule.


i am still working on the way majp suggested i make the schedule.



I hope ive done a decent job explaining all of this.


thanks again for your advise, i really do appreciate it.

Side note,



I remember mike60smart mentioning to normalize the data for the scheduling, but it slipped my mind and i never did that to the file i sent. anyways, i just want to throw that out there becasue he did a very good job explaining things i never knew about access.

Two very important design considerations for your tables;

1) You will need to be able to track your products by what materials are in them based purchase lot. ANYTHING that is consumed need to be able to be tracked back to original lots in case of recalls.

2) You will need to make sure you prevent any cross contamination. Can't have a peanut based product on the same line as a peanut free product.

Mark,



Thank you for that last post. There are systems in place to prevent cross-contamination. Luckily the company has enough equipment to produce more at once. That is a great advantage because a full breakdown and clean in-between batches is not always necessary.

Hey Everyone, this thread is solved thanks be to mike60smart, majp, mark, and everyone else who posted their ideas. There is no harm in posting more if you think of anything that might have been missed. Most recent activity was from mike60smart. We got together and he showed me what changes were make to the original DBschedule.



I cant thank you guys enough for the support. It seems like I have found a good community here. I wish i could do something to repay each of you for your help.


Cheers!



Attached is the Relationship diagram to show how the data is connected and normalized.