I have a table whose records consist of minor inventory. I need to sum the cost of these records and distribute the sum in another table based on the price of the record. for example. say the sum of the inventory is is $1000, and I have 4 jobs in the other table.
*table 2* *table 1* **job# amount InvAmnt** **item# cost** job1 $100,000 - 1 $250 job2 $50,000 - 2 $150 job3 $25,000 - 3 $100 job4 $25,000 - 4 $500
I need to sum everything in table 1 then based on the value of the amount in table 2. therefore, 50% of the sum would go to job1, 25% of the sum would go to job2, and 12.5% of the sum would go to job3 and job4. So the final table should look like this:
*table 2* **job# amount InvAmnt** job1 $100,000 $500 job2 $50,000 $250 job3 $25,000 $125 job4 $25,000 $125
how do I accomplish this?