Gathering information to print an employee payslip

[dewildpup]

I am having difficulty gathering the data for one. Here is the problem, when I try to gather the data using recordsets one gets to the end before the other and it gives an error message. Essentially it should work like this

Step 1: Get all employee who are to be paid
Step 2: For each employee get earnings & deductions for current period.
Step 3: Add EmployeeCode,earnings and deductions to payslip table.

Step 4: Create report with special formating features

Help me to gather the data first and then we will take it from there. I will give you an example, tbl is short for table.

tblEmployee: EmpleeCode = CATL01 has the followin earnings for the period:

tblEarnings: Salary 5000
Travel 600
Entert 600

tblDeductions:NIS 480
PAYE 75
from the above you want to build a table which the payslip data will be pull from to make the report.

tblPayslip should look like this:
EmpleeCode EarnCode EarnAmt DeductCode DeductAmt
CATL01 100 5000 005 480
CATL01 105 600 015 75
CATL01 110 600

 

[belovedcej]

could you provide your table definitions? It is unclear where your data is coming from. I believe we could help if we knew what fields are in which tables and how they are related.

 

[dewildpup]

I am providing the table definitions as requested.

tblEarnings: EmployeeCode,EarningsCode ,EarningsAmt

tblEmployee:EmployeeCode, and other employee details

tblDeduction:EmployeeCode,DeductionCode ,DeductionAmt

The tables are related by EmployeeCode. What I would like to do is select all earnings for an employee and sort then by EaningsCode. Base on the sort order assign a number which will represent the line it will print on.

For example Employee CATL01 have the following earning
in the tblEarnings:

EarningsCode EarnDescription EmployeeCode EarningsAmt
100 Salaries CATL01 5000
105 Travel CATL01 600
110 Entertainment CATL01 600

EarningsCode DeductnDescription EmployeeCode DeductionAmt
005 NIS CATL01 50.50
010 PAYE CATL01 37.50

I want to create a report that look like this:
EarCode EarningsAmt DedCode DedtDescrip DeductionAmt
100 $5,000.00 005 NIS $50.50
105 $600.00 010 PAYE $37.50
110 $600.00

Total $6,200.00 $88.00

Net Pay $6,112.00

I abbreviated the names on the payslip so that it could hold properly I hope you understand.

 

[lespaul]

How do you know that the NIS deduction "belongs" to the 100 EarCode? How do you know that the PAYE deduction "belongs" to the 105 Travel category? Or is what you really want all the additions to earnings on the left and all the deductions on the right?


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[dewildpup]

Actually you are right all the earnings are to be printed on the left and all the deductions on the right there really don't belong to each other. But if we assigned a line number to then both (earnings and deductions) we could join then by EmployeeCode and Line number so that a earnings will print on the same line as the deduction with that number.

For example: number the earnings as follows:

Line 1= 100 Salaries
Line 2= 105 Travel

number the deductions as follows:

Line 1= 005 NIS
Line 2= 010 Travel

The numbering is solely base on sorting (sort first and then assigned the number)

Now we can join the deductions an earnings table by employeeCode and line number.

The trick is there may be more line for deductions than earnings, and there may be more line for earnings than there are for deduction (the may or may not have the same amount of lines).

So we have to create an SQL statement that switches from a left join to a right join depending on which table have in the most line to return.

I hope this explains it better.

 

[dewildpup]

Listen guys I am yet to solve the problem and I really need your help.

 

[dewildpup]

Hi Guys,

I finally got the payslip information together. Now how do I write code so that a report has the same number of records on each page. E.g if the detailed section of a report has 6 records I want to add two blank lines to make eight lines. If the report has 10 records print 8 on the first page and print the other 2 on another page adding 6 blank lines to make eight lines on the second page.


regards

LeVere