I have two tables:

Salary Payslips:

Salary Deductions:

I have two tables "Salary Payslips" and "Salary Deductions." Deductions are removed from the total payslip amount (not shown here because its unnecessary to address the problem).

I am trying to calculate the total accrued deductions per salary cycle. (4/29/2022 -> 5/29/2022 is one salary cycle). In the example above, the total deductions between the two dates (i.e. the salary cycle) should be 200. I would want to have that number show up in the Salary Deduction field with Payslip ID=1 in the Salary Payslips table.

In the same fashion, the deduction on 3/30/2022 should show up in the salary payslips table at the record with payslip id =2.... and so on. The deduction amount in the payslip id = 3 should be zero since there were no deductions in during the period between 2/28-3/29.

This should be done where the employee name is identical in both tables, so "ABC has 50 deducted from salary in cycle between 2/28/2022 and 3/29/2022" etc.

All of this should be updated recursively in a form. Hence, the control should be able to query the tables for the data and parse that onto the corresponding field in the form (and by association, the table).

CodePudding user response：

Consider the DSum domain aggregate which is available as an expression function, VBA function, and SQL function in MS Access (frontend GUI but not backend connection). Similarly, you can use DLookUp (which I advised on your previous question) calling SUM() in expression argument.

Specifically, sum the Salary Deduction column in other table by corresponding Employee Name and where Deduction Date falls within date range of PaySlip Date and less than one month after using DateAdd.

Expression (set to control source of [Salary Deduction] textbox in form design of [Salary PaySlip] form)

=DSum("[Deduction Amount]", 
      "[Salary Deductions]", 
      "[Employee Name] = '" & [Employee Name] & "' AND
       [Deduction Date] >= #" & [PaySlip Date] & "#
                    AND <  #" & DateAdd("m", 1, [PaySlip Date]) & "#") 

VBA (programmatically calculate control source)

Forms![Salary PaySlip]![Salary Deduction] = DSum( _
    "[Deduction Amount]", _
    "[Salary Deductions]", _
    "[Employee Name] = '" & Forms![Salary PaySlip]![Employee Name] & "' AND " _ 
     & "[Deduction Date] >= #" & Forms![Salary PaySlip]![PaySlip Date] & "#" _
     & "             AND <  #" & DateAdd("m", 1, Forms![Salary PaySlip]![PaySlip Date]) & "#" _
)

SQL

UPDATE (using DSum to save data to table –cannot use subquery)

UPDATE [Salary PaySlip] p
SET p.[Salary Deduction] = DSum(
    "[Deduction Amount]", 
    "[Salary Deductions]", 
    "[Employee Name] = '" & p.[Employee Name] & "' AND
     [Deduction Date] >= #" & p.[PaySlip Date] & "#
                  AND <  #" & DateAdd("m", 1, p.[PaySlip Date]) & "#"
) 

Alternatively, you can use a correlated aggregate subquery which can have performance issues for large enough data. Hopefully, one day soon the MS Access team will add support for window functions (per SQL ANSI 2003) to the Access SQL dialect!

SELECT (using correlated aggregate subquery)

SELECT p.[PaySlip ID],
       (SELECT SUM([Deduction Amount])
        FROM [Salary Deductions] d
        WHERE d.[Employee Name] = p.[Employee Name]
          AND d.[Deduction Date] >= p.[PaySlip Date]
                             AND <  DateAdd('m', 1, p.[PaySlip Date])
       ) AS [Salary Deduction],
       p.[PaySlip Date]
       p.[Employee Name]
FROM [Salary Payslips] p