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
Any suggestions you guys have would be great as I am running into a wall right now.
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 **
(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)
(7) intStd (not included in the database running sum used to explain the issue)
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?
"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
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)