Solved

Sum Partial Records

Posted on 2014-04-18
12
231 Views
Last Modified: 2014-04-21
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
Comment
Question by:Zlda
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

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

Bill
0
 

Author Comment

by:Zlda
Comment Utility
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
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
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
0
 
LVL 14

Expert Comment

by:Bill Ross
Comment Utility
Hi,

You could also post a sample database.  

Bill
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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 Comment

by:Zlda
Comment Utility
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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

Expert Comment

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

Author Comment

by:Zlda
Comment Utility
can you give me a little code?
0
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
>>>>> 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
 
LVL 19

Accepted Solution

by:
Eric Sherman earned 500 total points
Comment Utility
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 varJobHoursSum
    Dim dteJobDate As Date
    
    Set rst = CurrentDb.OpenRecordset("SELECT Job_Number, Job_Hours, Job_Date FROM tblJobs ORDER BY Job_Number;", dbOpenDynaset)
    varJobHoursSum = 0
    dteJobDate = Date
    
    If rst.RecordCount > 0 Then
        
        rst.MoveFirst
        rst.Edit
        rst!Job_Date = dteJobDate
        rst.Update
        varJobHoursSum = varJobHoursSum + rst!Job_Hours
        rst.MoveNext
        
        Do Until rst.EOF
   
          varJobHoursSum = varJobHoursSum + rst!Job_Hours
          If varJobHoursSum > 24 Then
              dteJobDate = dteJobDate + 1
              varJobHoursSum = rst!Job_Hours
          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

Open in new window



HTH ... ET
0
 

Author Comment

by:Zlda
Comment Utility
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
 
LVL 19

Expert Comment

by:Eric Sherman
Comment Utility
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now