Creating the SQL which will make running-total fields in a new table

In this article, we’ll explore how to create a temporary table with running total fields for every value of a foreign key. We’ll also delve into why Access may ask for a specific value and provide a solution.

Understanding Running Totals

Running totals are a common feature used in databases to calculate cumulative values over a set period. They’re essential in various applications, including time tracking and payroll management. A running total is calculated by summing up the values of a column within a specific range, taking into account any changes or updates.

For instance, let’s say we have a table called Daily Timesheet with columns for TimesheetEntryID, EmployeeID, and HoursUsed. We want to create a running total for each employee’s hours used up to the current day. The running total would be calculated by summing up the hours used for all previous days.

Temporary Tables in SQL

A temporary table is a table that exists only during the duration of a query or stored procedure. It’s used to store intermediate results, perform calculations, or provide data for reporting purposes. Temporary tables are typically used with user-defined data types and have some limitations compared to permanent tables.

To create a temporary table, you need to specify # before the table name, followed by the schema, table name, and columns.

Subqueries in SQL

Subqueries are queries nested inside another query. They’re used to retrieve data based on conditions or calculations. Subqueries can be correlated (referencing the outer query’s rows) or non-correlated (not referencing the outer query’s rows).

In our case, we want to create a running total for each TimesheetEntryID. To achieve this, we need to use correlated subqueries.

The Problem with the Original Query

Let’s analyze why Access is asking for a specific value and what went wrong in the original query:

SELECT e.EmployeeName, d.TimesheetEntryID, 
       (SELECT SUM(sub_d.VacationHoursUsed - sub_d.VacationHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID
          AND sub_d.EmployeeID = d.EmployeeID) AS VacationHoursAvailable,

       (SELECT SUM(sub_d.BankedHoursUsed - sub_d.BankedHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID
          AND sub_d.EmployeeID = d.EmployeeID) AS BankedHoursAvailable,

       (SELECT SUM(sub_d.PersonalHoursUsed - sub_d.PersonalHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID
          AND sub_d.EmployeeID = d.EmployeeID) AS PersonalHoursAvailable

INTO [Hours Accumulation Temporary]

FROM [Employee Master] e 
INNER JOIN [Daily Timesheet] d ON e.EmployeeID = d.EmployeeID

ORDER BY e.EmployeeID, d.TimesheetEntryID

The problem lies in how we’re referencing the Daily Timesheet table inside our correlated subqueries. Currently, you are attempting to use the very table you intend to create (Hours Accumulation Temporary) inside your query when actually you want to point to the outer query field: [Daily Timesheet].TimesheetEntryID.

To fix this issue, we need to adjust our query by using aliases for subqueries. We’ll also add a condition to match on the same EmployeeID. By doing so, we ensure that each row in the outer query gets its own running total.

The Corrected Query

Here’s the revised query:

SELECT e.EmployeeName, d.TimesheetEntryID, 
       (SELECT SUM(sub_d.VacationHoursUsed - sub_d.VacationHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID
          AND sub_d.EmployeeID = e.EmployeeID) AS VacationHoursAvailable,

       (SELECT SUM(sub_d.BankedHoursUsed - sub_d.BankedHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID
          AND sub_d.EmployeeID = e.EmployeeID) AS BankedHoursAvailable,

       (SELECT SUM(sub_d.PersonalHoursUsed - sub_d.PersonalHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID
          AND sub_d.EmployeeID = e.EmployeeID) AS PersonalHoursAvailable

INTO #HoursAccumulationTemporary

FROM [Employee Master] e 
INNER JOIN [Daily Timesheet] d ON e.EmployeeID = d.EmployeeID

ORDER BY e.EmployeeID, d.TimesheetEntryID

Changes and Improvements

Here’s a summary of the changes made:

• We added aliases (sub_d and e) for subqueries to differentiate between the same named sources in the outer query and subquery.
• We included an additional condition in the WHERE clause to match on the same EmployeeID.
• We replaced [Daily Timesheet] with [Daily Timesheet] sub_d inside the correlated subqueries.

The Benefits of Using Aliases

Using aliases for correlated subqueries helps ensure that each row gets its own running total. Without proper aliasing, Access may request a specific value due to incorrect data retrieval.

For example, if you had used [Daily Timesheet].TimesheetEntryID as the alias in your original query:

SELECT e.EmployeeName, d.TimesheetEntryID, 
       (SELECT SUM(sub_d.VacationHoursUsed - sub_d.VacationHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID) AS VacationHoursAvailable,

       (SELECT SUM(sub_d.BankedHoursUsed - sub_d.BankedHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID) AS BankedHoursAvailable,

       (SELECT SUM(sub_d.PersonalHoursUsed - sub_d.PersonalHours)
        FROM [Daily Timesheet] sub_d
        WHERE sub_d.TimesheetEntryID <= d.TimesheetEntryID) AS PersonalHoursAvailable

INTO #HoursAccumulationTemporary

FROM [Employee Master] e 
INNER JOIN [Daily Timesheet] d ON e.EmployeeID = d.EmployeeID

ORDER BY e.EmployeeID, d.TimesheetEntryID

Access might request a specific value for [Daily Timesheet].TimesheetEntryID because it’s incorrectly treating the entire outer query as if it were a single value.

By using proper aliases, we can ensure that Access correctly retrieves data from our correlated subqueries and generates running totals for each row in the outer query.

Last modified on 2024-10-18