'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Calculate a Running Total for Labor Time in Microsoft Access
Question details
The user needs to multiply labor time by the number of employees and calculate a cumulative running total ordered by release date or ticket number.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Tracking cumulative rework or labor time across different tickets or release dates in a database.
- Observed behavior
- Requires a query to compute a running sum of labor time while properly accounting for Access storing time values as fractions of a day.
Ensure your tracking log query already includes fields for Release Date, Ticket, Labor Time, and Number of People before building the cumulative subquery.
Use a Correlated Subquery by Release Date
This is the standard SQL method for calculating a running total over time by comparing dates in the current row to previous rows.
A correlated subquery calculates a value for each row returned by the main query. By asking Access to sum all labor hours where the release date is less than or equal to the current row's release date, you generate a running total.
Open Microsoft Access, navigate to the Create tab, and click Query Design.
Right-click the query tab at the top of the workspace and select SQL View from the context menu.
Enter the following SQL code: SELECT Q1.Status, Q1.[Release Date], Q1.Ticket, Q1.Labor, Q1.[# of People], (SELECT Sum(Q2.Labor * Q2.[# of People]) FROM [Tracking Log Query] AS Q2 WHERE Q2.[Release Date] <= Q1.[Release Date]) AS TimeSpentToDate FROM [Tracking Log Query] AS Q1 ORDER BY Q1.[Release Date];
Click the Run button (!) in the Design ribbon to view the cumulative totals ordered by the release date.
Correlate the Running Total by Ticket Number or JobID
If you need cumulative totals grouped per ticket or specific jobs rather than chronologically, you must adjust the subquery's WHERE clause.
Convert Access Time Values to Hours or Minutes
Microsoft Access stores time as fractions of a 24-hour day. To display cumulative totals as standard hours or minutes, a mathematical conversion is required.
Track Labor Time and Totals Easily with WPS Spreadsheet
Complex SQL queries in Microsoft Access can be difficult to manage. WPS Spreadsheet provides a lightweight, intuitive, and free Microsoft Office alternative. You can calculate labor time and running totals seamlessly using simple spreadsheet formulas while maintaining full compatibility with Microsoft file formats.
- 1. Open WPS Spreadsheet: Create a new workbook or open your existing Excel tracking log.
- 2. Organize Your Data: Create columns for Ticket, Release Date, Labor Time, and Number of People.
- 3. Calculate the Running Total: Use a simple formula like =SUM($E$2:E2) in the running total column and drag it down to calculate instantly.
Frequently Asked Questions
Why do my time calculations look incorrect in Microsoft Access?
Microsoft Access stores time values internally as fractions of a day (e.g., 12 hours is stored as 0.5). To view these values as standard hours or minutes, you need to multiply the time field by 24 (for hours) or 1440 (for minutes).
What is a correlated subquery in Access?
A correlated subquery is a query nested inside another main query that references values from the outer query. It evaluates data row by row, making it the standard method in SQL for calculating running totals or cumulative sums.
How do I multiply labor time by the number of employees in a query?
In your query design grid, you can create a new calculated column by directly multiplying the two fields. For example, type TotalLabor: [Labor] * [# of People] in an empty Field cell.
