# Thread: Major Challenge - Update a table based on calculation

1. Registered User
Join Date
Mar 2003
Posts
225

## Unanswered: 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,

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

so that the output figure is separated

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...

Andy

2. Registered User
Join Date
Mar 2003
Posts
225
what a muppet!!!!

i have attached the wrong spreadsheet no wonder people cant see the calculation..

3. Registered User
Join Date
Mar 2003
Posts
225
is there please anyone who can offer me some advice with this.

4. Registered User
Join Date
Dec 2003
Posts
268

## Create a SubRoutine

You can create a subroutine accepting the three values then returning the values to thier respective cells.

on the current form

private sub DoThisItIsCool(Value1 as long, Value2 as long, Value3 as long)

txtH8Output.value = (value3*value2)/(Value1+Value2)
txtH7OutPut.value = (value3*value1)/(value1+value2)

end sub

Find some event that will do this updating and call the reference to the subroutine.

if the calculations get more complicated you can add logical operators to apply calculations based on the information passed in.

This shoud solve the problem for you.

MW

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•