Form setup for input of Work Orders with a One to Many Relationship to Skus

[HansBades]

1. I have a database that tracks work orders for a company. Each work order can have many skus of parts that need repaired. For instance, one work order can have a sku for door handle and one for electrical switch indicating both need to be repaired in a particular office. But there is a different approval code for each sku within the same work order. The door handle sku might have an approval code of 'cosmetic' approval while the light switch might have one called 'safety' code approval. Also, sometimes one individual sku can have two approval codes such as when a light switch sku is approved both for cosmetic and for safety reasons. Though most of the time they have only one. What is the best way to display this for input on a form when entering a new work order? There is one to many relationship between work orders and skus and there is a one to many relationship between skus and approval codes although most will have only one approval code. How should I display this on a form? Should I change the tables below to have a different field for sku1, then sku2, etc? Would each sku have a respective approval codes such as approval codesfor1 and approvalcodesfor2 referring respectively to sku1 and sku2? Any ideas? Maybe a subform that punches out to list the skus and their respective approval codes within each work order record? Any help is much appreciated!

Table WorkOrder

WorkOrderID	WorkOrderName	Skus	ApprovalCode
1	IT Closet Repair	Door Handle	Cosmetic
1	IT Closet Repair	Light Switch	Safety
2	Bathroom	Socket	Safety

SKU ID	SKUs
1	Door Handle
2	Light Switch
3	Socket

ApprovalCodeID	ApprovalCode
1	Cosmetic
2	Safety
3	LegalRequirement

[Welshgasman]

You do not store the descriptive names, your store their primary key?
You would need a table that shows what ApprovalCode code applies to whch SKU as well?

Search here for normalization or even google for it, but there are many threads here with direct links.

[CarlettoFed]

You could start with this hypothesis:

In the attachment you will find the example with the structure.
OrdersData.zip

[HansBades]

Super helpful! Thank you!

[HansBades]

A couple of follow up questions. First, when I create a form off the tblWorkOrders table it creates a subform with the WorkOrderSKUs table. This is great! And exactly what I wanted. But in the form design view, the subform is just a frame and doesn't give me the ability to insert a dropdown. I want to insert a dropdown so that when using this form to enter in work orders, I can drop down and choose the sku name such as "Door Handle" rather than have to know the SKUID for "Door Handle". Is there anything I can do?Also, I want to add some additional fields that qualify or are tied to the SKU itself within the work order. For instance, I want to create From and To fields related to the location of the SKU. For instance, if a work order requires a door handle, then I want to show the door handle used came from warehouse 1 and after the work order is finished it would be installed and thus located at Office Room 124. Could I create a location table and then add a location ID field to the WorkOrderSkus table? Thank you so much again!

[Micron]

the subform is just a frame

Perhaps you need to click on the subform control (what you're calling a frame, I think) then right click on it and choose 'subform in new window'. Or you can open the subform from the nav pane (as long as the main form isn't open) and edit. Either way, you need the subform in its own design window.
HTH

[HansBades]

Thank you. I created a form off the tblWorkOrders table which is linked to the tblWorkOrderSkus table (see diagram above). The form automatically creates a subform of the tblWorkOrderSkus table. All that is good. But there is no way I can see to edit the subform to include dropdowns. I tried with the right click but there was not an option to edit the subform in a new window.

[Micron]

Did you try my other suggestion - with main form closed, open subform in design view (right click on subform name that you see in the navigation pane on the left)i and open in design view? Perhaps I am not understanding exactly what it is you are trying to do. Uploading a copy of your db with explicit instructions is an option too. You copy your db, compact/repair the copy (database tools on the ribbon) and then zip the copy & post it. Posting instructions are available from the "how to attach files" button at the top of the forum header. You could remove from the copy db whatever isn't necessary to work with the problem.

[HansBades]

Yes, I tried your suggestion but when I right clicked there was no option. All I did was to create a form directly from the tblWorkOrders table. Because it is linked in a one to many relationship with the tblWorkSkus table, the form automatically created a subform of the tblWorkskus. However, I don't seem to have an option to edit it. Perhaps I need to create first a form only of the tblWorkOrders table and then separately of the tblWorkSkus table and then link the latter as a subform? Thanks again for your help.

[CarlettoFed]

Maybe it's best if you attach the file to see what you've created.