Windows 10 
Office 365
I had a table designed to capture SKUs parts.
SKUID SKUName SKUDescription SKUCategoryIDFK
Every SKU was required to have a corresponding SKUCategory. For instance, a washer ring may have fallen into the plumbing category.
But now, management wants the ability to relate a SKU to more than one SKUCategory. So a particular washer ring could for instance fall into the Plumbing and the Aluminum Parts Category.
Because I thought it was important to have a unique SKUID for each SKU rather than have multiple lines in the SKU table for what is the same SKU although it falls into multiple categories, I created a junction table.
SKUJunctionCatID SKUIDFK SKUCategoryIDFK
The junction table sits in between the SKUTable and SKUCategorytable as you suspect linked to the SKUID and SKUCategoryID respectively.
My forms now of course don't work properly and I have to modify them. For instance, before it was very easy to add a new SKU. Adding a new SKU required adding a SKU name and required adding the SKU category it was associated with. Since the SKU table had both the name and the foreign key for the SKUcategory, creating a form for entry was simple.
And entering Work Orders were also pretty simple. I have a WorkOrderTable as follows:
WorkOrderID WorkOrderName WorkOrderDescription DepartmentIDFK
And it was linked to a WorkOrderSKU table that listed all the parts needed for the workorder including the reasoning for needing that part.
WorkOrderSKUID WorkOrderIDFK SKUIDFK ReasonCodeIDFK
So entering in new Work Orders was also fairly simple. Before, the SKUIDFK was associated with a SKUID from the SKU table that always had one, but only one, SKU Category. The WorkOrderSKU table was my subform to my workorder form. Thus, it was very easy to enter a new workorder and then enter the multiple sku parts that were associated with it and the reason why each SKU was needed.
But now that a SKUID can have multiple categories, I need to be sure that the Work Order identifies which category the SKU is being associated with for this particular order. I am trying to determine the best way to do this.
The associated categories for the SKUs are now in the junction table, not in the SKU table. Each SKU in the SKU table can have multiple linked records in the junction table. Do I create a query as my subform that connects my workorderSKUtable to my junction table and use that as my subform? I was thinking that I would have a dropdown on the form that had two columns, one showing the SKU and the other showing the SKUCategory it falls in. Therefore, I would just need to choose the correct option depending on what category the SKU falls under for that particular work order. Does this sound right?
I appreciate your help. My work doesn't allow me to show more. Thank you again.
Reply With Quote
