By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,222 Members | 1,123 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
1 Reply


100+
P: 759
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:

Expand|Select|Wrap|Line Numbers
  1. [Employees]
  2. Employee_ID (Autonumber - Primary key)
  3. Employee Name (Text - Required)
  4. Hourly Rate (Double - Required)
  5. Etc.
  6.  
  7. Another table will be
  8. [Piecework]
  9. Piecework_ID (Autonumber - Primary key)
  10. Piecework_Total (Double - Required)
  11.  
  12. Now you can assign a piecework to an employee:
  13. [AssignedPieceworks]
  14. Assigned_ID (Autonumber - Primary key)
  15. Employee_ID (Lookup on table [Employees] - Required)
  16. Piecework_ID (Lookup on table [Piecework] - Required)
  17. Labor_Start (Date/Time - Required)
  18. Labor_End (Date/Time)
  19.  
  20. After that is a good idea to store partial payments:
  21. [PartialPayments]
  22. PartialPayment_ID (Autonumber - Primary key)
  23. Assigned_ID (Lookup on table [AssignedPieceworks]] - Required)
  24. Partial_Payment (Double - Required
  25.  

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.
Mar 17 '12 #2

Post your reply

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