Create a Running sum in detail of report 1

[dbanker]

I am trying to create a report that displays employees hours grouped by the day they work and would like to have a field that shows their accumulated time beside their hours for each day. Anybody's help would be deeply appreciated. Thanks

 

[dhookom]

Did you try a text box with the Running Sum property set to Over Group?

Duane
Hook'D on Access
MS Access MVP

 

[dbanker]

Yes I tried that but it totals the hours of all employees together for each day. I need each individual's hours totaled as each day progresses.

 

[dhookom]

You should have mentioned in your first post that you had tried the running sum and the results. If you want a running sum over each employee, you must group by employee.

Duane
Hook'D on Access
MS Access MVP

 

[dbanker]

Okay, sorry I didn't tell you everything I've already tried and the results, so here it goes. As I mentioned before I tried the sum properties set Over Group, I also tried grouping by the employee's last name with a sum textbox located in the group footer. Here is the problem with that. The output I'm looking for is to have a report that lists everyone who works on a day, the amount of hrs worked, and what their accumulated hrs are on that day. When I group by date then by employee last name with the sum textbox located in the lastname group footer, it totals all employees hrs that worked that day together. I need each individual's hrs totaled. I thought about using an IIF statement but because the names of the employees may change over time it seems like that might have to be pretty elaborate. I hope this helps to better explain my needs

 

[dhookom]

I am only more confused. You want "Running sum in detail of report" but suggest you are adding controls to a group footer section (not the detail section).

Perhaps you could remove my confusion by providing some sample records and how you want them displayed in your report.

Duane
Hook'D on Access
MS Access MVP

 

[dbanker]

Okay, I have two tables. EmployeeInformation which contains fields EmployeeNum (Auto), LastName, FirstName, Active (True/False), and EmployeeHours which has TransactionNum,(auto), EmployeeID (tables are joined on EmployeeNum & EmployeeID) TransactionDate, and HoursWorked.

The source for the report is a query containing fields LastName, FirstName, HoursWorked, TransactionDate, EmployeeID, and Active. It uses the criteria Active = "True" and TransactionDate Between [Enter Start Date] And [Enter End Date].

I would like the Report to display something like this:

Day Worked Last Name First Name Hours Worked Total Hrs
1-1-08
Baldwin Debbie 8 8
Banks Darnell 8 8
Clark Tony 6 6
1-2-08
Baldwin Debbie 8 16
Banks Darnell 8 16
1-3-08
Banks Darnell 8 24
Clark Tony 8 14

I hope this helps to lessen the confusion I've caused you. Thanks for your help.

 

[dhookom]

You would need to use a subquery in your report's record source query. It might look something like:

RunSumHrs:(SELECT Sum(HoursWorked) FROM ... S WHERE S.TransactionDate BETWEEN [Enter Start Date] and S.[TransactionDate] AND S.EmployeeNum = ...EmployeeNum)

Duane
Hook'D on Access
MS Access MVP

 

[nobull613]

You could try a DSum function in a new textbox in the details. The field names in the formula below will need to be changed to match what you have in your query and report.

=DSum("[HoursWorked]","Query Name","[EmployeeID]=" & [EmployeeID] & " and [TransactionDate] Between " & [Enter Start Date] & " and #" & [TransactionDate] & "#")

[Enter Start Date] should be the same as the criteria entry for the date range criteria.

Hope that helps.

 

[dbanker]

It looks like DSum function is exactly what I'm looking for I just need to get the Syntax right on it. Thanks a lot.

 

[dhookom]

The DSum() function could be very inefficient. I am fairly certain a subquery in the report's record source would run much faster and would limit the possibility of errors if the report is opened with a filter/where condition other than the date range.

Duane
Hook'D on Access
MS Access MVP