Zlda
asked on
Sum Partial Records
I have a query as such
Job No. Hours
100 8
101 14
107 7
109 1
110 9
112 21
I want to group the jobs not to exceed 24 hours. Output should be
Group Hours
1 22
2 17
3 21
can this be done?
Job No. Hours
100 8
101 14
107 7
109 1
110 9
112 21
I want to group the jobs not to exceed 24 hours. Output should be
Group Hours
1 22
2 17
3 21
can this be done?
ASKER
The group numbers are irrelevant. They are in the correct order. I just need them grouped based on not exceeding 24. So the first 2 are grouped. The 3rd would exceed 24. Then the next 3 would be grouped. Etc.
Hi,
I don't understand your question. Please post some sample data.
How does the Job No. and Hours relate to groups 1,2 and 3.
Thanks,
Bill
I don't understand your question. Please post some sample data.
How does the Job No. and Hours relate to groups 1,2 and 3.
Thanks,
Bill
Hi,
You could also post a sample database.
Bill
You could also post a sample database.
Bill
Queries work on set theory. There is no set of records that constitute a group that I can see (nor can either of the other posters). Therefore, you cannot do this with a query. That leaves you with VBA. You will need to create a code loop to cycle through records accumulating values. When the value exceeds 24, you back out the last addition, write a record and start the accumulation again with the current record. Then presumably you will do something with the new file you just created.
ASKER
vba is fine. Here is what I want to accomplish. I don't have the date column. The 'Date' column is what I am try to calculate. It will change date based on 24 hours.
Job No. Hours Date
100 8 4/18/2014
101 14 4/18/2014
107 7 4/19/2014
109 1 4/19/2014
110 9 4/19/2014
112 21 4/20/2014
113 43 4/21/2014
114 6 4/23/2014
115 5 4/23/2014
Job No. Hours Date
100 8 4/18/2014
101 14 4/18/2014
107 7 4/19/2014
109 1 4/19/2014
110 9 4/19/2014
112 21 4/20/2014
113 43 4/21/2014
114 6 4/23/2014
115 5 4/23/2014
OK. Give it a shot and we'll help if you get stuck. I outlined the logic in my previous post.
ASKER
can you give me a little code?
>>>>> The 'Date' column is what I am try to calculate. It will change date based on 24 hours.>>>>>
How do you determine the starting date or the date for the first group? In the example above, 04/18/2014. Is this based on the date you are running the function???
ET
How do you determine the starting date or the date for the first group? In the example above, 04/18/2014. Is this based on the date you are running the function???
ET
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
The function you sent works good. Thank You.
There are a couple of issues.
1st if the hours for 1 job is more than 24 (job 113 is 43 hours) it should be 2 days not 1.
Also, is there a way to split jobs. I.E. the first 2 jobs total 22 hours. Can it take 2 hours from the 3rd job (7) hours and use the remainder of 5 for the next day?
Maybe this should be a separate post? Any help you can provide would be appreciated.
There are a couple of issues.
1st if the hours for 1 job is more than 24 (job 113 is 43 hours) it should be 2 days not 1.
Also, is there a way to split jobs. I.E. the first 2 jobs total 22 hours. Can it take 2 hours from the 3rd job (7) hours and use the remainder of 5 for the next day?
Maybe this should be a separate post? Any help you can provide would be appreciated.
Anything is possible in code but your last comments are really another question. Plus, if you put together a sample Access table with a good representation of the data that shows all of the unique cases ... that will make it a lot easier to help you design a function.
Like other questions ...
1.) Can the very first job of the day exceed 24 hours???
2.) What is the maximum number of hours for a job?? Can it be greater than 24, 48, 72, 96??
ET
Like other questions ...
1.) Can the very first job of the day exceed 24 hours???
2.) What is the maximum number of hours for a job?? Can it be greater than 24, 48, 72, 96??
ET
Sure but the groups need to be in the Jobs somewhere. How do you determine the groups?
Bill