426,222 Members | 1,123 Online
Need help? Post your question and get tips & solutions from a community of 426,222 IT Pros & Developers. It's quick & easy.

How do I calculate a running balance or running sum in Access?

 P: 9 Hello, I am building a database to help me figure out what my costs are on each job. Part of those costs are labor (payroll) and I am paying the employees for each piece they complete, aka piecework. In order to comply with the labor law standards, I am paying employees minimum wage @ \$8.00/hr. While they are working on the piece, I pay the employee by the hour @ \$8 and deduct the amount I pull from their total piece price. When they complete the piece, I pay them any money they may have left over from the draws I made as they progressed in completing the project. I have built a Piecework table which includes the [Employee Name], [Job Name], [Piecework Total]. I have also built a Payroll query which calculates the [Total Hours] worked per day on each job for each [Employee Name] with the total amount of [Earnings] for each day. I would like to include the [Piecework Total] from my Piecework table and somehow build an expression that deducts the [Earnings] from each [Piecework Total] as the employee progressively works on the project. I know this might sound a little complex or confusing so here is an illustration of my database. Tables: [Piecework Total] Employee Name Job Name Piecework Total [Employees] Employee Name Hourly Rate Etc. Payroll Query Employee Name Date Hourly Rate Start Time End Time Total Hours: [End Time]-[Start Time] Earnings: [Total Hours]*[Hourly Rate] So here's what I would like to add to my new query or report (whichever will support the information I am trying to find): Piecework Total of \$500 assigned to John Smith for 1234 ABC Lane 3/5/2012- 8 hours, earnings \$64, 1234 ABC Lane \$436 3/6/2012- 8 hours, earnings \$64, 1234 ABC Lane \$372 3/7/2012- 8 hours, earnings \$64, 1234 ABC Lane \$308 3/8/2012- 8 hours, earnings \$64, 1234 ABC Lane \$244 3/9/2012- 8 hours, earnings \$64, 1234 ABC Lane \$180 John Smith completes the piece on 3/9/12 and collects the remaining balance of \$180. So what I am trying to do is calculate a running balance/sum. How do I do this in Access? Can I do this in a query or does it have to be done in a report? Please help. Mar 16 '12 #1