shahid kazi
asked on
i want to make a running total upto a certain value. once the value is acheived i want to restart running total from the new row
i have a query BalTanktoFabrication which shows the balance tanks to be fabricated.
now i want to calculate the manhours, so i have created a Form TankMHOurCal here i configure the tank specs and calculate the required manhours for each tank.
now further on Form TankMHourcal i have [#ofCrew] [Crew Hrs] and [Bal Hours] as a row and in the above of the form i have Total shift hours which no of employee multiplied by shift hours. for example if the shift hours is 11 and no of employees is 100 than 11x100=1100
my question, i want a text box on form TankMHourCal, which makes running sum of [Crew Hrs] until it reaches 1100 or [total shift hours]. after that it should start making the running sum again from new.
fab-prog-test.accdb
now i want to calculate the manhours, so i have created a Form TankMHOurCal here i configure the tank specs and calculate the required manhours for each tank.
now further on Form TankMHourcal i have [#ofCrew] [Crew Hrs] and [Bal Hours] as a row and in the above of the form i have Total shift hours which no of employee multiplied by shift hours. for example if the shift hours is 11 and no of employees is 100 than 11x100=1100
my question, i want a text box on form TankMHourCal, which makes running sum of [Crew Hrs] until it reaches 1100 or [total shift hours]. after that it should start making the running sum again from new.
fab-prog-test.accdb
ASKER
my plant is when it gets to the limit it should restart Dsum from begining
ASKER
sorry my plan is when it gets to the limit it should restart Dsum newly from that row.
ASKER
Below is my DSUM formula to calculate the running sum.
RunningSum: DSum("[CrewHours]","[TANKS
the result is as below, it is fine as per the function. but what i am looking is that once the sum of RunningSum reaches for eg. 1000, it should restart the sum newly from that row.
RunningSum
44
132
198
330
418
506
594
682
770
858
902
946
990
1056
1100
1408
1540
RunningSum: DSum("[CrewHours]","[TANKS
the result is as below, it is fine as per the function. but what i am looking is that once the sum of RunningSum reaches for eg. 1000, it should restart the sum newly from that row.
RunningSum
44
132
198
330
418
506
594
682
770
858
902
946
990
1056
1100
1408
1540
sorry my plan is when it gets to the limit it should restart Dsum newly from that row.I think you didn't understand the impact of the question. If the running sum is 1050 prior to record 212 and record 212 would add 100 hours. What happens to the "50" hours? One row in a query can't have two values.
ASKER
That will do with me no problem for that. I will use that balance 50 hours in next formula.
But i want to break the sum at that row and restart summing.
Pls help
But i want to break the sum at that row and restart summing.
Pls help
ASKER
I have made a query that shows the balance tanks to be fabricated. Now i want to plan for these tanks when to start Fabrication as per priority.
Suppose if i have 20 tanks to be fabricated, In my qery i have the list of tanks with qty and number of employees will work on each tank. If for eg. I have 10 employees and on each tank 2 people are working so i can start Fabrication for only 5 tanks at a time. If these employees work for 11 hrs per day, so 10 x11 hrs = 110 hrs. So after every 110 hrs approximately i want to break the sum and restart summing. This break i will use when the employees finish Fabrication of above 5 tanks then they will start with the next 5 tanks. It is just to get a start for the next batch of tanks. If u have better idea, pls advise
Suppose if i have 20 tanks to be fabricated, In my qery i have the list of tanks with qty and number of employees will work on each tank. If for eg. I have 10 employees and on each tank 2 people are working so i can start Fabrication for only 5 tanks at a time. If these employees work for 11 hrs per day, so 10 x11 hrs = 110 hrs. So after every 110 hrs approximately i want to break the sum and restart summing. This break i will use when the employees finish Fabrication of above 5 tanks then they will start with the next 5 tanks. It is just to get a start for the next batch of tanks. If u have better idea, pls advise
ASKER
Or other way using the dsum function when the number of employees reach 10 it should stop summing and restart summing newly. But i dont how to do it, pls pls help.
Queries operate on sets This isn't a set problem it is a sequential problem. The value in one record depends on the value from other records. Sequence is critical.
ASKER
If u consider the number as 10 as the place to stop than it becomes a sequential
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Running totals require rows to have a unique identifier and they require that the recordset be sorted by that identifier. If the identifier is not the primary sequence you want (date might be more appropriate), then you need to concatenate a sort field that will be unique by using some sequence field plus the unique identifier. I don't see a PK in your table. In a relational database, ALL tables should have a unique identifier. If there is no natural one, then you can use an autonumber.
The other thing you should understand is that in a relational database, no record is dependent on any other record and sequence can only be ensured by sorting on a unique identifier. What you are trying to do is a sequential process and although it is possible assuming you can come up with something unique to sort the recordset into the sequence you want to build the product in.