By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,118 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,118 IT Pros & Developers. It's quick & easy.

Count number of events daily within a date range.....

P: 26
In my Access '03 table I have three columns. P/N, StartDate,EndDate. Most of the time the start date and the end date are the same. Occasionally the end date can than a couple of days from the start date. I am trying to chart my workload for each day. So if a part is in the shop for 4 days, it gets counted for all 4 days.
Expand|Select|Wrap|Line Numbers
  1. P/N              StartDate                 EndDate
  2. 1                  1/1/08                      1/1/08
  3. 2                  1/2/08                      1/2/08
  4. 3                  1/2/08                      1/3/08
  5. 4                  1/1/08                      1/4/08
So the result would be:
Expand|Select|Wrap|Line Numbers
  1. Date         Total Parts worked
  2. 1/1/08            2
  3. 1/2/08            3
  4. 1/3/08            2      (Part 3 was still worked on the third)
  5. 1/4/08            1
I would like these results in a table.

Hope this makes sense, I could sure use the help.

Thanks
Aug 30 '08 #1
Share this Question
Share on Google+
31 Replies


NeoPa
Expert Mod 15k+
P: 31,473
I would think about a query which grouped by P/N and summed the value in each record reached by subtracting the [StartDate] from the [EndDate]+1.

What you do with this (display it or append the results into a table) is up to you.
Aug 30 '08 #2

P: 69
In my Access '03 table I have three columns. P/N, StartDate,EndDate. Most of the time the start date and the end date are the same. Occasionally the end date can than a couple of days from the start date. I am trying to chart my workload for each day. So if a part is in the shop for 4 days, it gets counted for all 4 days.
Expand|Select|Wrap|Line Numbers
  1. P/N              StartDate                 EndDate
  2. 1                  1/1/08                      1/1/08
  3. 2                  1/2/08                      1/2/08
  4. 3                  1/2/08                      1/3/08
  5. 4                  1/1/08                      1/4/08
So the result would be:
Expand|Select|Wrap|Line Numbers
  1. Date         Total Parts worked
  2. 1/1/08            2
  3. 1/2/08            3
  4. 1/3/08            2      (Part 3 was still worked on the third)
  5. 1/4/08            1
I would like these results in a table.

Hope this makes sense, I could sure use the help.

Thanks
I think NeoPa's approach might not give the answer you want, because if a P/N is worked on for more than one date you need to count it twice. To achieve this, you might need to use some VBA which loops through all the dates in the required range and tests dates in each P/N record, adding one to a counter if StartDate>= TestDate and EndDate <= TestDate. At EOF for the P/N table or query, you'd need to write a record to a temporary table, set the counter to zero and paddle through the P/N records again. You'd need to delete and create the TableDef at the start of the VBA sub. Once the sub is run, you can use the temporary table as the basis for a report.
Aug 31 '08 #3

P: 26
I think NeoPa's approach might not give the answer you want, because if a P/N is worked on for more than one date you need to count it twice. To achieve this, you might need to use some VBA which loops through all the dates in the required range and tests dates in each P/N record, adding one to a counter if StartDate>= TestDate and EndDate <= TestDate. At EOF for the P/N table or query, you'd need to write a record to a temporary table, set the counter to zero and paddle through the P/N records again. You'd need to delete and create the TableDef at the start of the VBA sub. Once the sub is run, you can use the temporary table as the basis for a report.

I do not have very much experience in VBA, How would I go about creating this?
Aug 31 '08 #4

ADezii
Expert 5K+
P: 8,628
  1. youmike was dead on with this one, I just had some spare time and implemented his logic.
  2. Assuming your Table Name is tblParts, and contains the following Fields:
    • [P/N] - {LONG}
    • [StartDate] - {DATE/TIME}
    • [EndDate] - {DATE/TIME}
  3. Create a New Table, and Name it tblPartsWorked. This Table will consist of only two Fields and will be populated with the actual results:
    • [Date] - {DATE/TIME}
    • [Total Parts Worked] - {INTEGER}
  4. Create the following Sub-Routine and place it within a Standard Code Module:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub CalculateTotalPartsWorked()
    2. Dim dteWorkDate As Date
    3. Dim intDateCounter As Integer
    4. Dim MyDB As DAO.Database
    5. Dim rstParts As DAO.Recordset
    6. Dim rstTotalPartsWorked As DAO.Recordset
    7. Dim intTotalWorked As Integer
    8.  
    9. Set MyDB = CurrentDb
    10.  
    11. Set rstParts = MyDB.OpenRecordset("tblParts", dbOpenSnapshot)
    12. Set rstTotalPartsWorked = MyDB.OpenRecordset("tblPartsWorked", dbOpenDynaset)
    13.  
    14. CurrentDb.Execute "Delete * From tblPartsWorked;", dbFailOnError    'Clear contents
    15.  
    16. For intDateCounter = 0 To 30        'Calculate for January 2008 only
    17.   dteWorkDate = DateAdd("d", intDateCounter, #1/1/2008#)
    18.     Do While Not rstParts.EOF
    19.       If dteWorkDate >= rstParts![StartDate] And dteWorkDate <= rstParts![EndDate] Then
    20.         intTotalWorked = intTotalWorked + 1
    21.       End If
    22.       rstParts.MoveNext
    23.     Loop
    24.       With rstTotalPartsWorked
    25.         .AddNew
    26.            ![Date] = dteWorkDate
    27.            ![Total Parts Worked] = intTotalWorked
    28.         .Update
    29.       End With
    30.       intTotalWorked = 0        'Reset to 0
    31.       rstParts.MoveFirst        'Start all over again
    32. Next
    33.  
    34. rstTotalPartsWorked.Close
    35. rstParts.Close
    36. Set rstTotalPartsWorked = Nothing
    37. Set rstParts = Nothing
    38.  
    39. 'Let's see the fruits of our labor
    40. DoCmd.OpenTable "tblPartsWorked", acViewNormal, acReadOnly
    41. DoCmd.Maximize
    42. End Sub
    43.  
  5. Output based on your data:
    Expand|Select|Wrap|Line Numbers
    1. Date    Total Parts Worked
    2. 1/1/2008        2
    3. 1/2/2008        3
    4. 1/3/2008        2
    5. 1/4/2008        1
    6. 1/5/2008        0
    7. 1/6/2008        0
    8. 1/7/2008        0
    9. 1/8/2008        0
    10. 1/9/2008        0
    11. 1/10/2008        0
    12. 1/11/2008        0
    13. 1/12/2008        0
    14. 1/13/2008        0
    15. 1/14/2008        0
    16. 1/15/2008        0
    17. 1/16/2008        0
    18. 1/17/2008        0
    19. 1/18/2008        0
    20. 1/19/2008        0
    21. 1/20/2008        0
    22. 1/21/2008        0
    23. 1/22/2008        0
    24. 1/23/2008        0
    25. 1/24/2008        0
    26. 1/25/2008        0
    27. 1/26/2008        0
    28. 1/27/2008        0
    29. 1/28/2008        0
    30. 1/29/2008        0
    31. 1/30/2008        0
    32. 1/31/2008        0
    33.  
  6. Download the Attachment and save yourself a lot of time!
Aug 31 '08 #5

NeoPa
Expert Mod 15k+
P: 31,473
I think NeoPa's approach might not give the answer you want, because ...
I think NeoPa's approach might not give the answer you want, because, as a total n00b, he answered the question you DIDN'T ask.

My bad.

If I can help further let me know, but I doubt you'll need it with these guys on the case.
Sep 1 '08 #6

P: 26
It worked perfectly, thank you!

Hindsight is 20/20, but if I add the date_time_started and date_time_completed in separate columns, would it be difficult to have it calculate hours and minutes per day parts were worked. Instead of calculating the number of parts, figure how much time was worked each day. The biggest problem would be if a part is in the shop more than a couple of days, 24 hours would have to be added to each day.

Thanks again for all your help.
Sep 2 '08 #7

ADezii
Expert 5K+
P: 8,628
It worked perfectly, thank you!

Hindsight is 20/20, but if I add the date_time_started and date_time_completed in separate columns, would it be difficult to have it calculate hours and minutes per day parts were worked. Instead of calculating the number of parts, figure how much time was worked each day. The biggest problem would be if a part is in the shop more than a couple of days, 24 hours would have to be added to each day.

Thanks again for all your help.
You should definitely have mentioned this up front but I'm sure that it can be incorporated into the logic. In my opinion, this would best be handled with the addition of 2 Date/Time Fields namely [StartTiime] and [EndTime]. Post some values along with your original data into this Fields, then post what the final results should be based on these times, as in:
Expand|Select|Wrap|Line Numbers
  1. P/N        StartDate          EndDate        StartTime        EndTime
  2. 1           1/1/08            1/1/08       <Enter Time>     <Enter Time>
  3. 2           1/2/08            1/2/08
  4. 3           1/2/08            1/3/08
  5. 4           1/1/08            1/4/08
Sep 2 '08 #8

P: 26
You should definitely have mentioned this up front but I'm sure that it can be incorporated into the logic. In my opinion, this would best be handled with the addition of 2 Date/Time Fields namely [StartTiime] and [EndTime]. Post some values along with your original data into this Fields, then post what the final results should be based on these times, as in:
Expand|Select|Wrap|Line Numbers
  1. P/N        StartDate          EndDate        StartTime        EndTime
  2. 1           1/1/08            1/1/08       <Enter Time>     <Enter Time>
  3. 2           1/2/08            1/2/08
  4. 3           1/2/08            1/3/08
  5. 4           1/1/08            1/4/08


Sorry, it was thought up after this was done. You know how it goes.

Here is the sample data. If it would be easier you can put the date and time in the same record. Also, the output can have hours and minutes in separate columns, or just output in total minutes. Whichever is easier for you.

Expand|Select|Wrap|Line Numbers
  1. P/NStartDate    EndDate    StartTime    EndTime
  2. 1    1/1/2008    1/1/2008    9:10    12:20
  3. 2    1/2/2008    1/2/2008    10:19    14:36
  4. 3    1/2/2008    1/3/2008    15:00    10:25
  5. 4    1/1/2008    1/4/2008    8:00    11:35
  6.  
  7.  
And the output:
Expand|Select|Wrap|Line Numbers
  1. Date              Hr      Min
  2. 1/1/2008    20    10
  3. 1/2/2008    37    41
  4. 1/3/2008    34    25
  5. 1/4/2008    11    35
  6.  
Sep 2 '08 #9

NeoPa
Expert Mod 15k+
P: 31,473
How about simply including the times in the [StartDate] and [EndDate] fields. Date/Time fields handle that quite well of course.

The checks may have to be amended somewhat, but I doubt it would be too cumbersome.
Sep 2 '08 #10

ADezii
Expert 5K+
P: 8,628
How about simply including the times in the [StartDate] and [EndDate] fields. Date/Time fields handle that quite well of course.

The checks may have to be amended somewhat, but I doubt it would be too cumbersome.
Hello NeoPa, you are of course 100% correct in your statement. It's just this fixation that I have about Dates and Times associated with those Dates, I just feel they should be separate entities, and not be incorporated into a single value.
Sep 2 '08 #11

ADezii
Expert 5K+
P: 8,628
Sorry, it was thought up after this was done. You know how it goes.

Here is the sample data. If it would be easier you can put the date and time in the same record. Also, the output can have hours and minutes in separate columns, or just output in total minutes. Whichever is easier for you.

Expand|Select|Wrap|Line Numbers
  1. P/NStartDate    EndDate    StartTime    EndTime
  2. 1    1/1/2008    1/1/2008    9:10    12:20
  3. 2    1/2/2008    1/2/2008    10:19    14:36
  4. 3    1/2/2008    1/3/2008    15:00    10:25
  5. 4    1/1/2008    1/4/2008    8:00    11:35
  6.  
  7.  
And the output:
Expand|Select|Wrap|Line Numbers
  1. Date              Hr      Min
  2. 1/1/2008    20    10
  3. 1/2/2008    37    41
  4. 1/3/2008    34    25
  5. 1/4/2008    11    35
  6.  
I'm having a little trouble figuring out exactly how you arrived at your Output Table listed above. Kindly provide a more detailed explanation, and definitely post A.M./P.M. along with your Time Values. Thanks.
Sep 2 '08 #12

ADezii
Expert 5K+
P: 8,628
Maybe I am interpreting the data incorrectly, (very possible, actually probable) but to me on 1/2/2008, Parts 2, 3, and 4 were in the Shop for the following Time Intervals:
Expand|Select|Wrap|Line Numbers
  1. P/N    StartDate    EndDate       StartTime    EndTime      Mins
  2. 2    1/2/2008      1/2/2008      10:19 AM      2:36 PM      257
  3. 3    1/2/2008      1/3/2008      3:00 PM      10:25 AM     1165
  4. 4    1/1/2008      1/4/2008      8:00 AM      11:35 AM      215
The Total Time (Minutes) Parts 2, 3, and 4 were in the Shop is 1,637 Minutes or 27 Hours and 17 Minutes. Your results indicate that this value should be 37 Hours and 41 Minutes.
Sep 2 '08 #13

NeoPa
Expert Mod 15k+
P: 31,473
... and definitely post A.M./P.M. along with your Time Values. Thanks.
As the times are posted in 24 hour format, are the AM / PM bits necessary?

It's always possible to work with the dates and times stored separately. It doesn't seem the natural way for me, but I expect that's a personal opinion.

The beauty of a lot of these things is that they can often be done more than one way. I'd stick with what you prefer unless and until you find it doesn't work well for you.
Sep 2 '08 #14

P: 26
Maybe I am interpreting the data incorrectly, (very possible, actually probable) but to me on 1/2/2008, Parts 2, 3, and 4 were in the Shop for the following Time Intervals:
Expand|Select|Wrap|Line Numbers
  1. P/N    StartDate    EndDate       StartTime    EndTime      Mins
  2. 2    1/2/2008      1/2/2008      10:19 AM      2:36 PM      257
  3. 3    1/2/2008      1/3/2008      3:00 PM      10:25 AM     1165
  4. 4    1/1/2008      1/4/2008      8:00 AM      11:35 AM      215
The Total Time (Minutes) Parts 2, 3, and 4 were in the Shop is 1,637 Minutes or 27 Hours and 17 Minutes. Your results indicate that this value should be 37 Hours and 41 Minutes.

You are correct with 27 hours and 17 minutes. My math was way off. How difficult would it be to create something?
Sep 2 '08 #15

ADezii
Expert 5K+
P: 8,628
You are correct with 27 hours and 17 minutes. My math was way off. How difficult would it be to create something?
I'll work on it later, and try to incorporate it into the previus output logic.
Sep 2 '08 #16

ADezii
Expert 5K+
P: 8,628
As the times are posted in 24 hour format, are the AM / PM bits necessary?

It's always possible to work with the dates and times stored separately. It doesn't seem the natural way for me, but I expect that's a personal opinion.

The beauty of a lot of these things is that they can often be done more than one way. I'd stick with what you prefer unless and until you find it doesn't work well for you.
As the times are posted in 24 hour format, are the AM / PM bits necessary?
Hello NeoPa, no they are not. It's just that I was coming up with totally different results, and wanted to be absolutely sure that the correct Time Entries were as stated. I basically made the assumption that the Ops results were correct, and mine were wrong. I perhaps thought that maybe the Time Fields were Text instead of Date Field and that the results were unpredictable. Thanks for the oversight.
Sep 2 '08 #17

P: 26
I'll work on it later, and try to incorporate it into the previus output logic.
Thank you very much. If it would be faster, just have it calculate the times and put them in a different table. It can be named tblPartsTime, with fields names: date, totalTime. Whatever is easiest for you.

Thanks again.
Sep 2 '08 #18

NeoPa
Expert Mod 15k+
P: 31,473
Hello NeoPa, ... Thanks for the oversight.
Oversight? Did I miss something?

Just kidding :D

I wasn't trying to oversee at all. Just saw the thread and thought the comments may help. When did you ever need overseeing anyway?
Sep 2 '08 #19

ADezii
Expert 5K+
P: 8,628
Oversight? Did I miss something?

Just kidding :D

I wasn't trying to oversee at all. Just saw the thread and thought the comments may help. When did you ever need overseeing anyway?
I actually meant thanks for the insight, sorry.
Sep 3 '08 #20

ADezii
Expert 5K+
P: 8,628
Thank you very much. If it would be faster, just have it calculate the times and put them in a different table. It can be named tblPartsTime, with fields names: date, totalTime. Whatever is easiest for you.

Thanks again.
Round Two!
  1. Add two Fields to tblParts, namely:
    1. [StartTime] - {DATE/TIME} - (Required = Yes)
    2. [EndTime] - {DATE/TIME} - (Required = Yes)
  2. Add one Field to tblPartsWorked, namely:
    1. [Total Time] - {TEXT 50}
  3. Create a Query called qryTimeIntervals which will calcuilate the Intervals in Minutes betyween each [StartTime]/[EndTime] pair. It will also account for the overnight factor. The SQL is posted below:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblParts.[P/N], tblParts.StartDate, tblParts.EndDate, tblParts.StartTime, tblParts.EndTime, IIf(DateDiff("n",[StartTime],[EndTime])<0,DateDiff("n",[StartTime],[EndTime])+1440,DateDiff("n",[StartTime],[EndTime])) AS Mins
    2. FROM tblParts;
  4. Create a Function called fCalcTotalTime() which will translate the Aggregate Interval Totals into something more meaningful. The code is listed below:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcTotalTime(intMinutes As Integer) As String
    2. Dim intHours As Integer
    3. Dim intMinutesLeft As Integer
    4.  
    5. Select Case intMinutes
    6.   Case 0
    7.     fCalcTotalTime = "N/A"
    8.   Case Is < 60
    9.     fCalcTotalTime = CStr(intMinutes) & " minute(s)"
    10.   Case 60
    11.     fCalcTotalTime = "1 hour(s)"
    12.   Case Else
    13.     intHours = Int((intMinutes / 60))
    14.     intMinutesLeft = (intMinutes - (intHours * 60))
    15.       If intMinutesLeft = 0 Then
    16.         fCalcTotalTime = CStr(intHours) & " hour(s)"
    17.       Else
    18.         fCalcTotalTime = CStr(intHours) & " hour(s) - " & _
    19.                          CStr(intMinutesLeft) & " minute(s)"
    20.       End If
    21. End Select
    22. End Function
  5. Modify your Base Code in the Sub-Routine CalculateTotalPartsWorked(). Rather than just noting the additions and changes, I simply posted the code with revisions:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub CalculateTotalPartsWorked()
    2. Dim dteWorkDate As Date
    3. Dim intDateCounter As Integer
    4. Dim MyDB As DAO.Database
    5. Dim rstParts As DAO.Recordset
    6. Dim rstTotalPartsWorked As DAO.Recordset
    7. Dim intTotalWorked As Integer
    8. Dim intTotalPartsMins As Integer
    9.  
    10. Set MyDB = CurrentDb
    11.  
    12. Set rstParts = MyDB.OpenRecordset("qryTimeIntervals", dbOpenSnapshot)
    13. Set rstTotalPartsWorked = MyDB.OpenRecordset("tblPartsWorked", dbOpenDynaset)
    14.  
    15. CurrentDb.Execute "Delete * From tblPartsWorked;", dbFailOnError    'Clear contents
    16.  
    17. For intDateCounter = 0 To 30        'Calculate for January 2008 only
    18.   dteWorkDate = DateAdd("d", intDateCounter, #1/1/2008#)
    19.     Do While Not rstParts.EOF
    20.       If dteWorkDate >= rstParts![StartDate] And dteWorkDate <= rstParts![EndDate] Then
    21.         intTotalWorked = intTotalWorked + 1
    22.         intTotalPartsMins = intTotalPartsMins + rstParts![Mins]
    23.       End If
    24.       rstParts.MoveNext
    25.     Loop
    26.        Debug.Print intTotalPartsMins
    27.       With rstTotalPartsWorked
    28.         .AddNew
    29.            ![Date] = dteWorkDate
    30.            ![Total Parts Worked] = intTotalWorked
    31.            ![Total Time] = fCalcTotalTime(intTotalPartsMins)
    32.         .Update
    33.       End With
    34.       intTotalWorked = 0        'Reset to 0
    35.       intTotalPartsMins = 0     'Reset to 0
    36.       rstParts.MoveFirst        'Start all over again
    37. Next
    38.  
    39. rstTotalPartsWorked.Close
    40. rstParts.Close
    41. Set rstTotalPartsWorked = Nothing
    42. Set rstParts = Nothing
    43.  
    44. 'Let's see the fruits of our labor
    45. DoCmd.OpenTable "tblPartsWorked", acViewNormal, acReadOnly
    46. DoCmd.Maximize
    47. End Sub
  6. Forget everything I just said and download the Attachment, why don't you! Any questions, feel free to ask. The code is fairly generic and can easily be modified to produce different results. I also posted the new Output for you. Take care.
OUTPUT: (tblPartsWorked)
Expand|Select|Wrap|Line Numbers
  1. Date    Total Parts Worked        Total Time
  2. 1/1/2008       2                 6 hour(s) - 45 minute(s)
  3. 1/2/2008       3                27 hour(s) - 17 minute(s)
  4. 1/3/2008       2                23 hour(s)
  5. 1/4/2008       1                 3 hour(s) - 35 minute(s)
  6. 1/5/2008       0                N/A
  7. 1/6/2008       0                N/A
  8. 1/7/2008       0                N/A
  9. 1/8/2008       0                N/A
  10. 1/9/2008       0                N/A
  11. 1/10/2008      0                N/A
  12. 1/11/2008       0                 N/A
  13. 1/12/2008       0                 N/A
  14. 1/13/2008       0                 N/A
  15. 1/14/2008       0                 N/A
  16. 1/15/2008       0                 N/A
  17. 1/16/2008       0                 N/A
  18. 1/17/2008       0                 N/A
  19. 1/18/2008       0                 N/A
  20. 1/19/2008       0                 N/A
  21. 1/20/2008       0                 N/A
  22. 1/21/2008       1                  1 hour(s)
  23. 1/22/2008       0                 N/A
  24. 1/23/2008       0                 N/A
  25. 1/24/2008       0                 N/A
  26. 1/25/2008       0                 N/A
  27. 1/26/2008       0                 N/A
  28. 1/27/2008       0                 N/A
  29. 1/28/2008       0                 N/A
  30. 1/29/2008       0                 N/A
  31. 1/30/2008       0                 N/A
  32. 1/31/2008       0                 N/A
  33.  
Sep 3 '08 #21

P: 26
Round Two!
  1. Add two Fields to tblParts, namely:
    1. [StartTime] - {DATE/TIME} - (Required = Yes)
    2. [EndTime] - {DATE/TIME} - (Required = Yes)
  2. Add one Field to tblPartsWorked, namely:
    1. [Total Time] - {TEXT 50}
  3. Create a Query called qryTimeIntervals which will calcuilate the Intervals in Minutes betyween each [StartTime]/[EndTime] pair. It will also account for the overnight factor. The SQL is posted below:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblParts.[P/N], tblParts.StartDate, tblParts.EndDate, tblParts.StartTime, tblParts.EndTime, IIf(DateDiff("n",[StartTime],[EndTime])<0,DateDiff("n",[StartTime],[EndTime])+1440,DateDiff("n",[StartTime],[EndTime])) AS Mins
    2. FROM tblParts;
  4. Create a Function called fCalcTotalTime() which will translate the Aggregate Interval Totals into something more meaningful. The code is listed below:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCalcTotalTime(intMinutes As Integer) As String
    2. Dim intHours As Integer
    3. Dim intMinutesLeft As Integer
    4.  
    5. Select Case intMinutes
    6.   Case 0
    7.     fCalcTotalTime = "N/A"
    8.   Case Is < 60
    9.     fCalcTotalTime = CStr(intMinutes) & " minute(s)"
    10.   Case 60
    11.     fCalcTotalTime = "1 hour(s)"
    12.   Case Else
    13.     intHours = Int((intMinutes / 60))
    14.     intMinutesLeft = (intMinutes - (intHours * 60))
    15.       If intMinutesLeft = 0 Then
    16.         fCalcTotalTime = CStr(intHours) & " hour(s)"
    17.       Else
    18.         fCalcTotalTime = CStr(intHours) & " hour(s) - " & _
    19.                          CStr(intMinutesLeft) & " minute(s)"
    20.       End If
    21. End Select
    22. End Function
  5. Modify your Base Code in the Sub-Routine CalculateTotalPartsWorked(). Rather than just noting the additions and changes, I simply posted the code with revisions:
    Expand|Select|Wrap|Line Numbers
    1. Public Sub CalculateTotalPartsWorked()
    2. Dim dteWorkDate As Date
    3. Dim intDateCounter As Integer
    4. Dim MyDB As DAO.Database
    5. Dim rstParts As DAO.Recordset
    6. Dim rstTotalPartsWorked As DAO.Recordset
    7. Dim intTotalWorked As Integer
    8. Dim intTotalPartsMins As Integer
    9.  
    10. Set MyDB = CurrentDb
    11.  
    12. Set rstParts = MyDB.OpenRecordset("qryTimeIntervals", dbOpenSnapshot)
    13. Set rstTotalPartsWorked = MyDB.OpenRecordset("tblPartsWorked", dbOpenDynaset)
    14.  
    15. CurrentDb.Execute "Delete * From tblPartsWorked;", dbFailOnError    'Clear contents
    16.  
    17. For intDateCounter = 0 To 30        'Calculate for January 2008 only
    18.   dteWorkDate = DateAdd("d", intDateCounter, #1/1/2008#)
    19.     Do While Not rstParts.EOF
    20.       If dteWorkDate >= rstParts![StartDate] And dteWorkDate <= rstParts![EndDate] Then
    21.         intTotalWorked = intTotalWorked + 1
    22.         intTotalPartsMins = intTotalPartsMins + rstParts![Mins]
    23.       End If
    24.       rstParts.MoveNext
    25.     Loop
    26.        Debug.Print intTotalPartsMins
    27.       With rstTotalPartsWorked
    28.         .AddNew
    29.            ![Date] = dteWorkDate
    30.            ![Total Parts Worked] = intTotalWorked
    31.            ![Total Time] = fCalcTotalTime(intTotalPartsMins)
    32.         .Update
    33.       End With
    34.       intTotalWorked = 0        'Reset to 0
    35.       intTotalPartsMins = 0     'Reset to 0
    36.       rstParts.MoveFirst        'Start all over again
    37. Next
    38.  
    39. rstTotalPartsWorked.Close
    40. rstParts.Close
    41. Set rstTotalPartsWorked = Nothing
    42. Set rstParts = Nothing
    43.  
    44. 'Let's see the fruits of our labor
    45. DoCmd.OpenTable "tblPartsWorked", acViewNormal, acReadOnly
    46. DoCmd.Maximize
    47. End Sub
  6. Forget everything I just said and download the Attachment, why don't you! Any questions, feel free to ask. The code is fairly generic and can easily be modified to produce different results. I also posted the new Output for you. Take care.
OUTPUT: (tblPartsWorked)
Expand|Select|Wrap|Line Numbers
  1. Date    Total Parts Worked        Total Time
  2. 1/1/2008       2                 6 hour(s) - 45 minute(s)
  3. 1/2/2008       3                27 hour(s) - 17 minute(s)
  4. 1/3/2008       2                23 hour(s)
  5. 1/4/2008       1                 3 hour(s) - 35 minute(s)
  6. 1/5/2008       0                N/A
  7. 1/6/2008       0                N/A
  8. 1/7/2008       0                N/A
  9. 1/8/2008       0                N/A
  10. 1/9/2008       0                N/A
  11. 1/10/2008      0                N/A
  12. 1/11/2008       0                 N/A
  13. 1/12/2008       0                 N/A
  14. 1/13/2008       0                 N/A
  15. 1/14/2008       0                 N/A
  16. 1/15/2008       0                 N/A
  17. 1/16/2008       0                 N/A
  18. 1/17/2008       0                 N/A
  19. 1/18/2008       0                 N/A
  20. 1/19/2008       0                 N/A
  21. 1/20/2008       0                 N/A
  22. 1/21/2008       1                  1 hour(s)
  23. 1/22/2008       0                 N/A
  24. 1/23/2008       0                 N/A
  25. 1/24/2008       0                 N/A
  26. 1/25/2008       0                 N/A
  27. 1/26/2008       0                 N/A
  28. 1/27/2008       0                 N/A
  29. 1/28/2008       0                 N/A
  30. 1/29/2008       0                 N/A
  31. 1/30/2008       0                 N/A
  32. 1/31/2008       0                 N/A
  33.  
Thank you for putting the time in on this. Unfortuneately the results you show dont match up with what we mentioned earlier. 1/4/08 should be 11 hours 35 minutes, and 1/1/ should have a lot more than 6 hours on it.

Sorry to be a bother.
Sep 3 '08 #22

ADezii
Expert 5K+
P: 8,628
Thank you for putting the time in on this. Unfortuneately the results you show dont match up with what we mentioned earlier. 1/4/08 should be 11 hours 35 minutes, and 1/1/ should have a lot more than 6 hours on it.

Sorry to be a bother.
Not a bother, the hard work is done, it may be simply a matter of recoding the logic. Please tell me where I am incorrect in the following cases
  1. On 1/4/2008, P/N #4 was the only part in the Shop from 8:00 A.M. until 11:35 A.M., which is 215 minutes or 3 hours and 35 minutes.
  2. On 1/1/2008, P/N #1 was in the Shop from 9:10 A.M. until 12:20 P.M., which is 190 minutes or 3 hours and 10 minutes. P/N #4 was also in the Shop on 1/1/2008 from 08:00 A.M. until 11:35 A.M., which is 215 minutes or 3 hours and 35 minutes. The Total time that P/N #1 and 4 were in the Shop on 1/1/2008 is 405 minutes or 6 hours and 45 minutes.
  3. Please tell me if I am going about this the wrong way, or if my logic is flawed. To me it would be an impossibility that one or two outcomes would be correct while the other two are flawed, either they are all correct or all wrong.
  4. I must admit that you really have me puzzled on this one.
Sep 3 '08 #23

P: 26
Not a bother, the hard work is done, it may be simply a matter of recoding the logic. Please tell me where I am incorrect in the following cases
  1. On 1/4/2008, P/N #4 was the only part in the Shop from 8:00 A.M. until 11:35 A.M., which is 215 minutes or 3 hours and 35 minutes.
  2. On 1/1/2008, P/N #1 was in the Shop from 9:10 A.M. until 12:20 P.M., which is 190 minutes or 3 hours and 10 minutes. P/N #4 was also in the Shop on 1/1/2008 from 08:00 A.M. until 11:35 A.M., which is 215 minutes or 3 hours and 35 minutes. The Total time that P/N #1 and 4 were in the Shop on 1/1/2008 is 405 minutes or 6 hours and 45 minutes.
  3. Please tell me if I am going about this the wrong way, or if my logic is flawed. To me it would be an impossibility that one or two outcomes would be correct while the other two are flawed, either they are all correct or all wrong.
  4. I must admit that you really have me puzzled on this one.

Part 4 started on 1/1 and ended on 1/4. So the breakdown would be 16 hours (8am to Midnight) on day one, 24 hours on day 2, 24 hours on day 3, and 10 hours 35 minutes (Midnight to 10:35 am) on day 4.

Expand|Select|Wrap|Line Numbers
  1. Day        Part    Hours    Minutes
  2. 1            1        3           10
  3.              4       16           0
  4.  
  5. 2            2        4           17
  6.              3        9           0
  7.              4        24          0
  8.  
  9. 3            3        10          25
  10.              4        24          0
  11.  
  12. 4            4        11          35
  13.  
I am trying to capture tme worked per day, we operate 24 hours a day. I want to see where the manhours are going.

Hope this helps.
Sep 3 '08 #24

ADezii
Expert 5K+
P: 8,628
Part 4 started on 1/1 and ended on 1/4. So the breakdown would be 16 hours (8am to Midnight) on day one, 24 hours on day 2, 24 hours on day 3, and 10 hours 35 minutes (Midnight to 10:35 am) on day 4.

Expand|Select|Wrap|Line Numbers
  1. Day        Part    Hours    Minutes
  2. 1            1        3           10
  3.              4       16           0
  4.  
  5. 2            2        4           17
  6.              3        9           0
  7.              4        24          0
  8.  
  9. 3            3        10          25
  10.              4        24          0
  11.  
  12. 4            4        11          35
  13.  
I am trying to capture tme worked per day, we operate 24 hours a day. I want to see where the manhours are going.

Hope this helps.
Gotcha, but unfortunately this adds an entirely new level of complexity. I put too much time into this Thread to simply give up so I'm gonna retreat and put my thinking cap back on and see what I can do. Stay tuned.
Sep 3 '08 #25

NeoPa
Expert Mod 15k+
P: 31,473
Seems like you're captured on this one ADezii :D

BTW ...
I actually meant thanks for the insight, sorry.
I was just playing with words - I knew what you meant really ;)
Sep 3 '08 #26

ADezii
Expert 5K+
P: 8,628
Part 4 started on 1/1 and ended on 1/4. So the breakdown would be 16 hours (8am to Midnight) on day one, 24 hours on day 2, 24 hours on day 3, and 10 hours 35 minutes (Midnight to 10:35 am) on day 4.

Expand|Select|Wrap|Line Numbers
  1. Day        Part    Hours    Minutes
  2. 1            1        3           10
  3.              4       16           0
  4.  
  5. 2            2        4           17
  6.              3        9           0
  7.              4        24          0
  8.  
  9. 3            3        10          25
  10.              4        24          0
  11.  
  12. 4            4        11          35
  13.  
I am trying to capture tme worked per day, we operate 24 hours a day. I want to see where the manhours are going.

Hope this helps.
I'm getting there slow but sure, it seems to be a little more complex, at least to me, than I realized. Download the Attachment and see what you think:
Sep 3 '08 #27

ADezii
Expert 5K+
P: 8,628
Part 4 started on 1/1 and ended on 1/4. So the breakdown would be 16 hours (8am to Midnight) on day one, 24 hours on day 2, 24 hours on day 3, and 10 hours 35 minutes (Midnight to 10:35 am) on day 4.

Expand|Select|Wrap|Line Numbers
  1. Day        Part    Hours    Minutes
  2. 1            1        3           10
  3.              4       16           0
  4.  
  5. 2            2        4           17
  6.              3        9           0
  7.              4        24          0
  8.  
  9. 3            3        10          25
  10.              4        24          0
  11.  
  12. 4            4        11          35
  13.  
I am trying to capture tme worked per day, we operate 24 hours a day. I want to see where the manhours are going.

Hope this helps.
Hello freeflyer30339, I think that I am realllllllllllllllllly close now. I'll post the Base Code and as usual, make the Database available as an Attachment. BTW, it is a seperate DB, and not tied into the prior one. It still needs clean up work, this is just a rough draft. Keep in touch.
Expand|Select|Wrap|Line Numbers
  1. Public Sub CalculateTotalPartsWorked_2()
  2. Dim dteWorkDate As Date
  3. Dim intDateCounter As Integer
  4. Dim MyDB As DAO.Database
  5. Dim rstParts As DAO.Recordset
  6. Dim rstTotalPartsWorked As DAO.Recordset
  7. Dim intTotalWorked As Integer
  8. Dim intTotalPartsMins As Integer
  9.  
  10. Set MyDB = CurrentDb
  11.  
  12. Set rstParts = MyDB.OpenRecordset("qryTimeIntervals_2", dbOpenSnapshot)
  13. Set rstTotalPartsWorked = MyDB.OpenRecordset("tblPartsWorked_2", dbOpenDynaset)
  14.  
  15. CurrentDb.Execute "Delete * From tblPartsWorked_2;", dbFailOnError    'Clear contents
  16.  
  17. For intDateCounter = 0 To 30        'Calculate for January 2008 only
  18.   dteWorkDate = DateAdd("d", intDateCounter, #1/1/2008#)
  19.     Do While Not rstParts.EOF
  20.       If dteWorkDate >= rstParts![SDate] And dteWorkDate <= rstParts![EDate] Then
  21.         With rstTotalPartsWorked
  22.           .AddNew
  23.             ![Date] = dteWorkDate
  24.             ![Part#] = rstParts![P/N]
  25.             If rstParts![SDate] = rstParts![EDate] Then
  26.               ![Total Time] = fCalcTotalTime(DateDiff("n", rstParts![StartDate], rstParts![EndDate]))
  27.             ElseIf DateDiff("d", rstParts![SDate], rstParts![EDate]) >= 1 And dteWorkDate = rstParts![SDate] Then
  28.               ![Total Time] = fCalcTotalTime(DateDiff("n", rstParts![StartTime], #12:00:00 AM#) + 1440)
  29.             ElseIf DateDiff("d", rstParts![SDate], rstParts![EDate]) >= 1 And dteWorkDate = rstParts![EDate] Then
  30.               '![Total Time] = fCalcTotalTime(DateDiff("n", rstParts![EndTime], #12:00:00 AM#) + 1440)
  31.               ![Total Time] = fCalcTotalTime(DateDiff("n", #12:00:00 AM#, rstParts![EndTime]))
  32.             Else
  33.               ![Total Time] = "24 hours"
  34.             End If
  35.           .Update
  36.         End With
  37.       End If
  38.       rstParts.MoveNext
  39.     Loop
  40.       rstParts.MoveFirst        'Start all over again
  41. Next
  42.  
  43. rstTotalPartsWorked.Close
  44. rstParts.Close
  45. Set rstTotalPartsWorked = Nothing
  46. Set rstParts = Nothing
  47.  
  48. 'Let's see the fruits of our labor
  49. DoCmd.OpenReport "rptTotalHoursPartsInShopByDate", acViewPreview
  50. End Sub
Sep 4 '08 #28

P: 26
Hello freeflyer30339, I think that I am realllllllllllllllllly close now. I'll post the Base Code and as usual, make the Database available as an Attachment. BTW, it is a seperate DB, and not tied into the prior one. It still needs clean up work, this is just a rough draft. Keep in touch.
Thanks again for all your time and help on this. I have been combing through 15 thousand records and going cross eyed because of it. This script will definitely become a lifesaver.

Can you recommend any books or tutorials to start learning VB?
Sep 4 '08 #29

ADezii
Expert 5K+
P: 8,628
Thanks again for all your time and help on this. I have been combing through 15 thousand records and going cross eyed because of it. This script will definitely become a lifesaver.

Can you recommend any books or tutorials to start learning VB?
I'm actually sending you a revised Version of the Database. While the base code is still in tact, this newer Version should be more efficient. I won't post the revised code, I'll simply send the DB as an Attachment. Good luck!
Sep 4 '08 #30

P: 26
I'm actually sending you a revised Version of the Database. While the base code is still in tact, this newer Version should be more efficient. I won't post the revised code, I'll simply send the DB as an Attachment. Good luck!
ADezii......You ROCK!!!! Works like a champ. Thank you very much!!!!!
Sep 4 '08 #31

ADezii
Expert 5K+
P: 8,628
ADezii......You ROCK!!!! Works like a champ. Thank you very much!!!!!
You are quite welcome, it was a little challenge! (LOL).

P.S. - One little change that you may have noticed is that you can now plug in your own Date Range, by substituting your own Date Values for the Start and End Constants. The remainder of the code will do the rest (setting the Outer Loop).
Expand|Select|Wrap|Line Numbers
  1. '********* Plug in your own Range **********
  2.                                           '*
  3. Const conStartDate As Date = #1/1/2008#   '*
  4. Const conEndDate As Date = #1/31/2008#    '*
  5.                                           '*
  6. '*******************************************
Sep 4 '08 #32

Post your reply

Sign in to post your reply or Sign up for a free account.