Major Challenge - Update A Table Based On Calculation

I am so interested to see a solution to this little problem…i think ithis is one of my more complicated questions!!!!

Problem:

Where I work we have a series of machines… named “ C1, H7, H8 etc”

Each line has 3 counters on it,

Impressions – records the number of cartons used on the line
Standard Pack – records the number of filled cartons with product
Output – records the number of cartons that complete the whole line process and are ready to be shipped to the customer.

Sounds straight forward enough….but the process gets a little more complicated.

Some lines are referred to as joint lines… this indicates that the product from each line the output figure is joined together and totalled. So it is not possible to calculate how many each line completed for the output figure. Note… it is only the Output figure that is joint, each line still has it’s own impressions and standard park counters

Grouped lines are given a group code – P8 in this case.

Example…

C1 not joined

H7 and H8 joined.

C1 –
Impressions –1000 Cartons
Standard Pack – 900 Cartons
Output – 800 Cartons

H7 - P8
Impressions –1600 Cartons
Standard Pack – 1500 Cartons

H8 – P8
Impressions –400 Cartons
Standard Pack – 300 Cartons

P8
Output – 1600 Cartons

The output counter is brought in to the database automatically via a linked spreadsheet and a macro, this data is then stored in a table called “DaycodeMachineShiftImpressions” you will see in this table that the grouping is referred to as Machine.

As previously stated we have no real way of working out the amount each line has output individually.. the way it is calculated at the moment, is to take the figure for P8 and split it into 2 equal figures and assign that to each line.

So in this case the figure assigned to each line would be 800, although you will see that this is not possible for H8 line as it only put 400 cartons on the line in the first place.

I now want to implement the following calculation to my DB,

(see Attached Spreadsheet)

the calculations a re stored in cells b12 & b13 in the spreadsheet.

so that the output figure is separated

in proportion to impressions made.

I then need the figure per line to be fed back to the “tblProduction” output field..

in the tblproduction the data for P8 is stored to line level so in this case as H7 and H8, hence why i want to use the calculation to work out the output figure per line...

Can this be done automatically, if so please help me…

Andy