Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003

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


    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.


    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

    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…

    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2003
    what a muppet!!!!

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

  3. #3
    Join Date
    Mar 2003
    is there please anyone who can offer me some advice with this.

  4. #4
    Join Date
    Dec 2003

    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.


Posting Permissions

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