You are here:

Using MS Access/Ms Access - VB Report (Payroll)

Advertisement


Question
QUESTION: Hi Scottgem! I'm still stuck with my payroll system. I have finished the vb coding an creating the report(payslip).I have two combo box fields in my form containing Month and Year. Such that if a user choses month "May" year "2014" and presses the print button, the employee's payslips for May 2014 are printed. The challenge I am having is that, if an employee has a loan of say (500,000.00) monthly installments of (20,000.00)and starts to pay the loan in November 2014. Then the November 2014 payslip should have the installment and net-loan reflected as 20,000.00 (480,000.00). Same should apply for subsequent months i.e. December payslip should have 20,000.00 (460,000.00).  I have tried my best for months now but I don't seem to figure this out.  Kindly help me out of this hell.

ANSWER: Without knowing your table structure I really can't help much.

I can tell you the way I would probably set this up is with a deductions table:

tblDeductions
DeductionID (PK Autonumber)
PayCheckID (FK to specific paycheck in tblPaycheck)
DeductionTypeID (FK to lookup table of deduction types)
DeductionAmount
AssociationID (FK to LoanID, Benefit Plan etc.)

So the 20,000 payment would be a record in this table linked to the Nov & Dec paychecks. You would have a query to calculate Loan Balance that might look like this:

SELECT LoanID, Sum(LoanAmount) As Loan, SUM(DeductionAmount) As Payments, Sum(LoanAmount)-Sum(DeductionAmount) AS Balance
INNER JOIN on tblLoans.LoanID = tblDeductions.AssociationID
FROM tblLoans, tblDeductions
GROUP BY LoanID;

You would then join to this query in your query to produce a paycheck to show the Balance.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Thanks for your timely response Scot. I have two tables 1- Employee_table with fields EmpID, FName, LName,Basic_Sal, hseAll, trAll, totalLoan, monthlyinstallment,no_of_months. 2- tblTotalLoan with fields EmpLoansID, EmpID, totalLoan,monthlyinstallment,bankName,dteFirstInstallment.

My problem is how to print payslips with reduced Loan amount every subsequent  month once the employee starts paying from the date specified in dteFirstInstallment for all employees.
I haven't gotten what you meant by having a deduction table. Will I be forced to hard code it every month? For instance, if I have a loan that has to be settled in a span of two years. Will I store like from the mentioned 20,000.00 * 24 months? What of other employees having a different pay amount?

ANSWER: If Employee_Table is your general employee table, it should not have the loan info in it. You seem to be duplicating the loan info in tblTotalLoan which is structured correctly. But you should have a separate table for all the deductions per pay period. You can automate generating a deduction record for each payment.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA


---------- FOLLOW-UP ----------

QUESTION: Thanks Scott, I'll create a deduction table. But how do I automate generating a reduction record for each payment? Any example. I still can't get myself on the go since not all employees start paying back the loan at the same month. Please don't give up on my questions.

Answer
Well this is a whole picture kind of thing. I can't give you specific answers without knowing the detailed workings. I can only tell you how I would do it.

So in addition to the deduction table which details deductions by paycheck, you have an Employee Deduction table. Something like this:

tblEmpDeductions
EmpDeductionID (PK Autonumber)
DeductionTypeID (FK)
DeductionCalc
DeductionAmount
DateCreated
DateEnded

The DeductionCalc would indicate if the amount was a fixed value or a percentage or, maybe, a lookup. The Dates would indicate when the deduction starts and ends.

You then use a code process to loop through this table and calculate the actual amount of the deduction and then generate a record in tblDeductions for the pay period.

So if the Deduction type is a loan, then it would just generate a record for the loan amount. If the Deduction type is local taxes, that would be a percentage of salary or based on a lookup table. This all feeds your pay stub report.


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.