Sum consecutive rows with values > 0 in a field

NIMBUS

Registered User.
Local time
Today, 01:24
Joined
Mar 7, 2007
Messages
15
Hi all
I have a access table which I hopefully have correctly attached a snapshot of
here

The dates will always be consecutive and span from current date - 13 to current date +13
I am looking for something that, in a report, will sum consecutive days worked only, regardless of the year week no. So something that will sum the hours field in rows 2 & 3 then sum the hours field in rows 6 & 7 etc. Is this possible? Any help would be appreciated.
 

Attachments

  • Table.JPG
    Table.JPG
    31.8 KB · Views: 447
Have you tried creating a query that returns only the records you wish? Once you've done so, look at the TOTALS option in the ribbon and make the query do the work for you.
 
Thanks for that Mark but I'm not sure that will give me the correct result. I'm trying to sum the time worked in the hours field where the first consecutive days worked appear then move down to the next set of consecutive days worked and sum them and continue this to end of the table so that I get individual totals for each set of consecutive days worked.
 
Mark's suggestion will give you exactly what you want. Specify the date range in the criteria row then do an aggregate query to sum the hours
 
I can see where the o/p would need to filter out any records with 0 hours, but how then to determine if dates are consecutive or not?
 
Nimbus,

Have you tried creating a query that references not only the table but also an alias to the table, such that you can join based on the WorkDate-1?

For naming, you will also want to change "Name" and "Date" to more descriptive names. Both are functions in VBA and you will get very strange results if you are not careful.
 
Many thanks for the suggestions guys. I have tried many ways with query's but cannot get the result I want. As gasman say's how do I get the query to recognize sequential dates. My initial thoughts were to create a For Next statement nested inside a Do Loop. The Do Loop would loop through each record until EOF. The for next would be something like For X = 70 to 90. At each record an iff statement would look at the value of the Hours field and if it is zero change it to the value of X. This would give a unique value to each of the rows where there is no time worked. I could then use the group function on these values on the report to get what I want.
This sounds good but I'm not sure how to build the code and if it would worknor not
 
If you want to do this in code or in a query, my question is the same.

X + 0 still equals X. Why do you CARE that you summed a zero in the mix? If this is a school project, I might understand that you were given a "nasty" assignment. But the laws of math trump such cases.

Having said that, there is such a thing as writing the summation query to include a clause in the WHERE section such as " WHERE ... AND ( Hours <> 0 ) ... "
 
NIMBUS,

Can you tell us the significance of consecutive days? What is the purpose of such a report?
Does the work involved not respect/consider weekends or holidays?
You should also be aware that Name, Date and Hours are reserved words in Access.
 
Jdraw
Each employee is allowed to work a maximum 70 hours over conscutive days before having to take a 24 hour period off work. The purpose of the report is to allow a manager to look and see the total hours an employee has worked where the hours have been worked over consecutive days during a period spanning 13 days before the current date and what he is rostered to work over days 13 consecutive days after the current date. The manager will use this info to decide wether the employee can be allocated overtime or not.
There are other things the report will do such as counting the number of consecutive shifts worked (max allowed is 13) and counting the number of 12 hour shifts worked but I can do all these quite easily.
thanks for the advise re naming which Mark mentioned earlier. I am aware of the naming protocol but used these in my example just for speed.

The doc Man
I was hoping that the iff statement in the For Next procedure would increment the value of x one step at a time and replace each o with the incremented value which could then be used to group the consecutive days worked in the report.
The query you suggest will only total all the hours where the value is not zero where as I want sub totals based on consecutive days worked.
 
I've recently been working on a fatigue management system for fire fighters where it is not the number of hours but the consecutive days on a wild fire front cannot exceed 5. A single day off resets the count.

I will presume this is your situation but you are working in a period of D-13 to D+13 but any day with zero hours would reset the count.

This function which I modified from my roster system, will calculate the number of hours worked and rostered in such a period.

Code:
Function GetConsecutiveWorkHours(PersonID As Long, CheckDate As Date)
   Dim db As Database, rst As Recordset
   Dim lngHours As Long
   
   Set db = CurrentDb
   
   '--Open recordset covering 13 days before/after
   Set rst = db.OpenRecordset("SELECT * FROM tblFireHours where PersonID=" & PersonID & " AND WorkDate Between #" & Format(CheckDate - 13, "mm/dd/yyyy") & "# AND #" & Format(CheckDate + 13, "mm/dd/yyyy") & "#")
   
   '--Get hours worked before CheckDate
   rst.FindFirst "WorkDate = #" & Format(CheckDate - 1, "mm/dd/yyyy") & "#"
   If Not rst.NoMatch Then
      Do While Not rst.BOF
         If rst!HoursWorked > 0 Then
            lngHours = rst!HoursWorked + lngHours
         Else
            '--Have found a non work day, so finish with earlier dates
            Exit Do
         End If
         rst.MovePrevious
      Loop
   End If
   
   '--Get hours on Check date and for future days
   rst.FindFirst "WorkDate = #" & Format(CheckDate, "mm/dd/yyyy") & "#"
   If Not rst.NoMatch Then
      Do While Not rst.EOF
         If rst!HoursWorked > 0 Then
            lngHours = rst!HoursWorked + lngHours
         Else
            '--Have found a non work day, so finish with later dates
            Exit Do
         End If
         rst.MoveNext
      Loop
   End If
   
   GetConsecutiveWorkHours = lngHours
   rst.Close: Set rst = Nothing
   Set db = Nothing
   
End Function
 
Brilliant that looks like just what I needed thank you Cronk. I am away for a few days now but will try it when I get back and let you know if it works
Thanks to all who offered help. It is really appreciated.
 
Many Thanks Cronk. I was able to use your code and after some alterations to suit my needs it works fine and does just what I wanted
 
@Cronk,

Lucky hot shots. When I had to do that we didn't get days off.

Well, only lucky if they get paid for the "mandatory break". :)
 

Users who are viewing this thread

Back
Top Bottom