Using Access to calculate sum loop

[RussOSU]

I am not really a programmer so I am not sure if this is as easy as it seems in my head. Here is the background;

We have a highly variable attendance rate which leads many days to us building subpar products due to guys not having enough time to finish all the work. Our management has finally started to come around to the management style of adjusting our output based off the attendance. We have been developing a database that pulls in all the information we need and it is working pretty good. The issue I am having however is the calculation.

Our data comes in sorted in sequential order. What I am trying to do is say " x people show up * y work minutes/shift = available", "a # of product * b std min/product = std" x*y <= a*b. I want to solve this for a however b is constrained by the # of a built. To do this I thought of using a while loop and then just count the number of iterations the loop runs through. However I am not sure how to do this in access using queries. It has been 5 years since I wrote a database so I still have the training wheels on and am not sure the next step on this. Any help would be greatly appreciated

-Russ

- Russ

 

[Remou]

I am not quite sure I get what you are doing. Do you mean something like (SQL view):
[tt]SELECT tblProducts.ProductID, tblProducts.Product, tblProducts.ProductionMinutes, [Enter No Attendees: ] AS Attendees, [Attendees]*400 AS AvailableMins, [AvailableMins]/[ProductionMinutes] AS NoItems
FROM tblProducts;[/tt]

 

[RussOSU]

for explaination I will put a table (that does not exist) but maybe raw data could help.

Area employ work_min avail_min
1 34 44 1496
2 30 44 1320
3 5 40 200
4 25 44 1100

Area 1

prod_# std_prod
1 321.1
2 300.5
3 342.0
4 298.0
5 310.6

In this example Area 1 has 1496 available. I am trying to find out if they can build 4 or 5 prod. To do this I am wanting to do a cumu total of the std_prod. So up to prod #4 the std total is 1261.6 adding in #5 you get 1572.2. #4 is less then the available and #5 is above so I would want to return 4 prod can be made at this attendance. I hope this helps.

- Russ

 

[Remou]

Is this any closer:
Query 1
[tt]SELECT tblStd_Prod.[prod_#], tblStd_Prod.std_prod, (select sum(std_prod) from tblstd_prod A where a.[prod_#]<=tblstd_prod.[prod_#]) AS CumlSum
FROM tblStd_Prod;[/tt]
Query 2
[tt]SELECT tblAvail_Min.avail_min, (Select Max([Prod_#]) from qryCumulativeSum where CumlSum<=avail_min) AS No_Prods
FROM tblAvail_Min;[/tt]

 

[RussOSU]

Won't your query 1 always be true as you are referencing the same tables in each? a.prod_# is populated by prod_#? The other issue I have is that the sequence numbers do reset back to 1. If it just happens that say one day we build 51 and the next day we build 50 it would return a 1 for the possible builds as this would be #1 sequence again correct?

- Russ

 

[Remou]

I'm afraid I do not quite understand, there is nothing to be true in query one, it simply sums std_prod cumulatively so that the second query can find 4 [(Select Max([Prod_#]) from qryCumulativeSum where CumlSum<=avail_min)] for this line:
[tt]Area employ work_min avail_min
1 34 44 1496[/tt]

I though that this was what you wanted:
"So up to prod #4 the std total is 1261.6 adding in #5 you get 1572.2. #4 is less then the available and #5".
I see now that I should probably have limited the second query to area one.
I do not quite get the second part of waht you are saying either. The sample data you gave starts at 1, so 4 is returned.
[tt]prod_# std_prod
1 321.1
2 300.5
3 342.0
4 298.0
5 310.6[/tt]
If this table started at prod_# 50, 53 would have been returned, based on the other data, that is, the equivalent of 4, which seemed to be the answer you wanted. What should it be?

 

[RussOSU]

I just threw 50 out there sorry. Using the example if 5 is the max number scheduled and we can only build 4. When I run this again for a different shift/day I need it to start where it stopped (#5) and then go to the next day which would be #1 on the new day. Just taking the code you have and trying to figure out how it works it appeared to me that the second time I ran it, if it starts it will miss represent the # of units if it is returning the sequence. Sorry my example sucked I have a very very large data set that I was just trying to represent quickly. Thank you for your help

- Russ

 

[Remou]

I am probably still missing an important point, but it seems to me that you could modify the cumulative sum query (query one) to accept a start number. You would probably use a form, but roughly:
[tt]SELECT tblStd_Prod.[prod_#], tblStd_Prod.std_prod, (select sum(std_prod) from tblstd_prod A where a.[prod_#]<=tblstd_prod.[prod_#] And a.[prod_#]>=[Enter Start No: ]) AS CumlSum
FROM tblStd_Prod
WHERE (((tblStd_Prod.[prod_#])>=[Enter Start No: ]));[/tt]