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

[Chelle8263]

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.

[Mihail]

First of all I think that yo need a good lecture:
http://bytes.com/topic/access/insigh...ble-structures

After you read that you will understand (I hope) that every table must have an ID field (preferable AutoNumber) that unique identify a record in that table.
As example your database should look like this:

[Employees]
Employee_ID (Autonumber - Primary key)
Employee Name (Text - Required)
Hourly Rate (Double - Required)
Etc.
 
Another table will be
[Piecework]
Piecework_ID (Autonumber - Primary key)
Piecework_Total (Double - Required)
 
Now you can assign a piecework to an employee:
[AssignedPieceworks]
Assigned_ID (Autonumber - Primary key)
Employee_ID (Lookup on table [Employees] - Required)
Piecework_ID (Lookup on table [Piecework] - Required)
Labor_Start (Date/Time - Required)
Labor_End (Date/Time)
 
After that is a good idea to store partial payments:
[PartialPayments]
PartialPayment_ID (Autonumber - Primary key)
Assigned_ID (Lookup on table [AssignedPieceworks]] - Required)
Partial_Payment (Double - Required
 

Hope this is a help for you to start your database.
Of course this is not a "template" because is very possible that I misunderstand the real situation.