Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2006
    Posts
    8

    Unanswered: Sum Loop With VBA

    It has been 5 years since I did any Access/VBA programming and it has come back at me full speed. I have taken a couple days to read up and try and get a grasp on my diminished (to almost nothing) programming skills I am back to try this again Here is some data below, there is a lot of data as I wanted to show a couple of the issues I am coming up on.

    Here is what I am trying to do, currently we have a build rate set in stone (50 a day), no matter how many people show up to work. What we are trying to get to is an adjustible build rate determined by how many people show up. (i.e. 50% of people show up so we build 50% of the product).

    The method I am attempting to do this with is a running sum (shown below in tblLabor is a copy of the excel I would use if I manually computed this).

    I am having problems with my current code with completing a sum loop. I thought setting temp sum (intSum) and adding the data from the table to it would complete what I was looking for however I get a (no current record) error.

    Some of the issues I have been having trouble with how to handle are;
    1. At the beginning of each shift the sequence starts over.
    2. I need to calculate this for each production area(Pool_ID) which is around 90 areas
    3. They are looking to have this reported for the 5 day week for 2 shifts.

    My initial thought was to use VBA since I remembered the Do While Loop existed. I am not sure if this is the best approach though as people have suggested SQL for it. Here is the link to the SQL thread I started

    http://www.tek-tips.com/viewthread.c...211&page=1

    Any suggestions you guys have would be great as I am running into a wall right now.

    -Russ

    Here is the current state of the code;
    -----------------------------------------------------------
    Dim dbBrenda As Database
    Dim tblLabor As Recordset
    Dim tblTrac As Recordset
    Dim intStd As Integer
    Dim intAvailable As Integer
    Dim intCount As Integer

    **I have removed the SQL just to try and clean this up a little **

    intCount = 0
    intStd = 0

    Do While intStd < intAvailable

    intCount = intCount + 1
    intStd = intStd + sgSTANDARD_MINUTES
    tblLabor.MoveNext

    Loop
    -----------------------------------------------------------

    (1) txPOOL_ID (needs to handle from 01 - 99)
    (2) byDAY_INDEX (contains all 0, 1, 2, 3, 4)
    (3) txSHIFT_ID (can contain 1, 2, 3)
    (4) bySHIFT_SEQUENCE_NO (scheduled and resets back to 1 each day)
    (5) txSERIAL (individual trucks don't need the data later)
    (6) sgSTANDARD_MINUTES
    (7) intStd (not included in the database running sum used to explain the issue)
    (8) ntINHOUSE
    (9) ntWORK_MINUTES_ACTUAL
    (10) intAvailable


    tblLabor
    (1) (2) (3) (4) (5) (6) | (7)
    ----------------------------------------------------------
    01 0 1 1 W60927 484.849 | 484.849
    01 0 1 2 X74563 402.616 | 887.465
    01 0 1 3 X72403 333.231 | 1220.696
    01 0 1 4 U33895 406.196 | 1626.892
    01 0 1 5 U54169 428.064 | 2054.956
    01 0 1 6 X71387 403.037 | 2457.993
    01 0 1 7 U33896 406.196 | 2864.189
    01 0 1 8 X35570 401.648 | 3265.837
    01 0 1 9 X74564 402.616 | 3668.453
    01 0 1 10 X74042 399.499 | 4067.952
    01 0 1 11 W87861 411.947 | 4479.899
    01 0 1 12 X72829 390.72 | 4870.619
    01 0 1 13 W87862 411.947 | 5282.566
    01 0 1 14 X72104 397.473 | 5680.039
    01 0 1 15 X72102 397.473 | 6077.512
    01 0 1 16 X75155 400.212 | 6477.724
    01 0 1 17 X72284 431.937 | 6909.661
    01 0 1 18 X74581 389.54 | 7299.201
    01 0 1 19 X72161 409.475 | 7708.676
    01 0 1 20 X18465 399.121 | 8107.797
    01 0 1 21 X14596 389.693 | 8497.49
    01 0 1 22 X72162 409.475 | 8906.965
    01 0 1 23 X45523 314.411 | 9221.376
    01 0 2 1 W55900 409.358 | 9630.734
    01 0 2 2 X72105 397.473 | 10028.207
    01 0 2 3 X51190 425.456 | 10453.663
    01 0 2 4 W94660 389.942 | 10843.605
    01 0 2 5 X72106 397.473 | 11241.078
    01 0 2 6 X74043 399.499 | 11640.577
    01 0 2 7 X72103 397.473 | 12038.05
    |

    tblAvailable
    (2) (1) (3) (8) (9) (10)
    -----------------------------------------------
    0 01 1 27 440 11880
    0 01 2 27 440 11880
    1 01 1 31 440 13640
    1 01 2 27 440 11880
    2 01 1 31 440 13640
    2 01 2 27 440 11880

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Could you provide a detailed description of your various tables and their structure? Something tells me you need to get a shift_id going here...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2006
    Posts
    8
    The 2 tables I use are at the bottom tblLabor and tblAvailable. the (#) at the top refer to the descriptions above. (3) refers to txSHIFT_ID.

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    How many clients will be connecting to the access database at any given time? If more than one, will more than one be trying to update the records at any given time?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  5. #5
    Join Date
    Apr 2006
    Posts
    8
    There will be 1 person connected to the database at any time (probably just myself) and the data will be refreshed prior to the calculations being made. The idea behind this is that I can automatically generate the report (more quickly then manually calculating this for 80+ production areas)

  6. #6
    Join Date
    Apr 2006
    Posts
    8
    Does anyone have any ideas on a direction I should go with this? I am lost on even the next path to take to get this to work. Thanks

    -Russ

Posting Permissions

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