x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 240

# 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?
0
Zlda
• 4
• 3
• 3
• +1
1 Solution

Commented:
Hi,

Sure but the groups need to be in the Jobs somewhere.  How do you determine the groups?

Bill
0

Author Commented:
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.
0

Commented:
Hi,

How does the Job No. and Hours relate to groups 1,2 and 3.

Thanks,

Bill
0

Commented:
Hi,

You could also post a sample database.

Bill
0

Commented:
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.
0

Author Commented:
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
0

Commented:
OK.  Give it a shot and we'll help if you get stuck.  I outlined the logic in my previous post.
0

Author Commented:
can you give me a little code?
0

Accountant/DeveloperCommented:
>>>>> 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
0

Accountant/DeveloperCommented:
Try this function ... Assuming you will start the date incements based on the date you are running the funcction.  If not, just adjust the code accordingly.

``````Function CalcJobDates()
DoCmd.SetWarnings False
DoCmd.Hourglass True
Dim rst As DAO.Recordset
Dim dteJobDate As Date

Set rst = CurrentDb.OpenRecordset("SELECT Job_Number, Job_Hours, Job_Date FROM tblJobs ORDER BY Job_Number;", dbOpenDynaset)
dteJobDate = Date

If rst.RecordCount > 0 Then

rst.MoveFirst
rst.Edit
rst!Job_Date = dteJobDate
rst.Update
rst.MoveNext

Do Until rst.EOF

dteJobDate = dteJobDate + 1
End If

rst.Edit
rst!Job_Date = dteJobDate
rst.Update
rst.MoveNext
Loop
End If

DoCmd.Hourglass False

If Not rst Is Nothing Then
rst.Close
Set rst = Nothing
End If
MsgBox "Finished calculating Job Dates ...."
End Function
``````

HTH ... ET
0

Author Commented:
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.
0

Accountant/DeveloperCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.