I have been searching the message board for help and not sure if I am using the wrong terms in my search!.
I need some help with the following with a desktop application.
We are a manufacturing company, for example lets say a computer company...
I have a Parts table that contains all the parts that we use to build a PC. In this table I have the quantity on hand and assigned/sold then balance available.
The next table is the Bill of Materials. In the table I list the PC model, then in a set, look up the Parts table and it writes to a table called PC_BOM. This shows every part that goes into that PC, table is called BOM_Parts. Some parts are unique to this PC and others are common to all PCS plus there are multiples, for example: Screws x 6. The BOM_Parts table is linked naturally in the set.
Next, I have an Invioce_items table, that we sell a PC, it looks at the PC_BOM table. the Invoice_Items table records the sale and then posts (using the post feature in the Field Rules) the amount sold to the PC_BOM table.
Now comes my problem and where I am stuck. I need to post the parts in the bill of materials to the parts table, using the example above:
Invoice_Items table records the sale and posts the PC_BOM that 1 PC model XYZ has been sold.
Table BOM_Parts list all the items that are needed to bill PC model XYZ, for example Screws x 6.
Now I am lost but what I wants to do:
Using the example above, post a subtraction to the Parts Table, in this case Screws x 6. The database would need to read that 1 PC model XYZ has been sold then look at the PC_BOM table to get the part numbers and how many of these parts are used (Screws x 6) then post this to the Parts table, Screws -6, this would be posted to the sold field So we can see how many have been used / allocated and the balance on hand.
I hope that I have explained this well enough and I am sure there are posts abot this but I can not find this type of inventory control.
I need some help with the following with a desktop application.
We are a manufacturing company, for example lets say a computer company...
I have a Parts table that contains all the parts that we use to build a PC. In this table I have the quantity on hand and assigned/sold then balance available.
The next table is the Bill of Materials. In the table I list the PC model, then in a set, look up the Parts table and it writes to a table called PC_BOM. This shows every part that goes into that PC, table is called BOM_Parts. Some parts are unique to this PC and others are common to all PCS plus there are multiples, for example: Screws x 6. The BOM_Parts table is linked naturally in the set.
Next, I have an Invioce_items table, that we sell a PC, it looks at the PC_BOM table. the Invoice_Items table records the sale and then posts (using the post feature in the Field Rules) the amount sold to the PC_BOM table.
Now comes my problem and where I am stuck. I need to post the parts in the bill of materials to the parts table, using the example above:
Invoice_Items table records the sale and posts the PC_BOM that 1 PC model XYZ has been sold.
Table BOM_Parts list all the items that are needed to bill PC model XYZ, for example Screws x 6.
Now I am lost but what I wants to do:
Using the example above, post a subtraction to the Parts Table, in this case Screws x 6. The database would need to read that 1 PC model XYZ has been sold then look at the PC_BOM table to get the part numbers and how many of these parts are used (Screws x 6) then post this to the Parts table, Screws -6, this would be posted to the sold field So we can see how many have been used / allocated and the balance on hand.
I hope that I have explained this well enough and I am sure there are posts abot this but I can not find this type of inventory control.
Comment