Query no record display

[smtazulislam]

'Get "Run-Time error" this line, not execute sql'

   CurrentDb.Execute strSql

 

[Minty]

I'm confused - If you are trying to use @MajP functions then you should be using them as per his examples.
If you are stuck with inserting the date using the old methods then it is because it needs to have # # around it.

Numbers - No delimiterss
Strings - ' or """ delimiters
Dates - # # delimiters

See Allen Browne http://allenbrowne.com/ser-36.html for a function that will solve that issue.

 

[arnelgp]

Private Sub cmdUpdate_Click()
If Me.Dirty Then Me.Dirty = False

Dim CrId As Integer
Dim StrEmployeeID As Long
Dim StrYear As Long
Dim StrMonth As Long
Dim StrGrossSalary As Double
Dim StrTotalAllowances As Double
Dim StrTotalAbsentdays As lonh
Dim StrTotalDeductions As Double
Dim StrTotalOvertime As Double
Dim StrCashAdvance As Double
Dim StrSQL As String


CrId = Me.CurrentRecord
StrEmployeeID = Me.txtEmployeeID
StrYear = Me.cboYear
StrMonth = Me.CboMonth
StrGrossSalary = [Forms]![frmPayrollMainForm]![SubfrmEmployeeSalaries].[Form]![txtGS]
StrTotalAllowances = [Forms]![frmPayrollMainForm]![SubfrmAllowanceData].[Form]![txtTotalAllowances]
StrTotalAbsentdays = [Forms]![frmPayrollMainForm]![SubfrmAbsentDay].[Form]![txtTotalAbsent]
StrTotalDeductions = [Forms]![frmPayrollMainForm]![SubfrmDeductionProcedure].[Form]![txtTotalDeductions]
StrTotalOvertime = [Forms]![frmPayrollMainForm]![SubfrmOverTime].[Form]![txtTotalOverTime]
StrCashAdvance = [Forms]![frmPayrollMainForm]![SubfrmCashAdvance].[Form]![txtTotalCashAdvance]

StrSQL = "UPDATE INTO tblPayroll SET PayrollYear = p0, PayrollMonth = p1, WorkedDays = p2, GrossSalary = p3, TotalAllowances = p4, TotalAbsentdays = p5, TotalDeductions = p6, TotalOvertime = p7, CashAdvance = p8 " & _
         "WHERE EmployeeID = p9;"
          
'StrSQL = "UPDATE INTO tblPayroll SET PayrollYear = " & StrYear & ", PayrollMonth = " & StrMonth & ", WorkedDays = " & Me.txtDayOfMonth & ", GrossSalary = " & StrGrossSalary & ", TotalAllowances = " & StrTotalAllowances & ", TotalAbsentdays = " & StrTotalAbsentdays & ", TotalDeductions = " & StrTotalDeductions & ", TotalOvertime = " & StrTotalOvertime & ", CashAdvance = " & StrCashAdvance & "" & _
'         "WHERE EmployeeID = " & StrEmployeeID & ";"
    
    With CurrentDb.CreateQueryDef(vbNullString, StrSQL)
        .Parameters("p0") = StrYear
        .Parameters("p1") = StrMonth
        .Parameters("p2") = Me.txtDayOfMonth
        .Parameters("p3") = StrGrossSalary
        .Parameters("p4") = StrTotalAllowances
        .Parameters("p5") = StrTotalAbsentdays
        .Parameters("p6") = StrTotalDeductions
        .Parameters("p7") = StrTotalOvertime
        .Parameters("p8") = StrCashAdvance
        .Parameters("p9") = StrEmployeeID
        
        .Execute dbFailOnError
        
    End With
    
    MsgBox "Enter your Data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"
      
    'Debug.Print StrSQL
    'CurrentDb.Execute StrSQL, dbFailOnError

    DoCmd.GoToRecord , , acGoTo, CrId
End Sub

 

[smtazulislam]

Thank you so for your reply. This is the code I understand easily. Thank you.
First I try to your same code you posted. But error
then I try to the below code now :

If Me.Dirty Then Me.Dirty = False

Dim CrId As Integer
Dim StrGrossSalary As Double
Dim StrTotalAllowances As Double
Dim StrTotalAbsentdays As Double
Dim StrTotalDeductions As Double
Dim StrTotalOvertime As Double
Dim StrCashAdvance As Double
Dim StrSQL As String


CrId = Me.CurrentRecord
StrGrossSalary = [Forms]![frmPayrollMainForm]![SubfrmEmployeeSalaries].[Form]![txtGS]
StrTotalAllowances = [Forms]![frmPayrollMainForm]![SubfrmAllowanceData].[Form]![txtTotalAllowances]
StrTotalAbsentdays = [Forms]![frmPayrollMainForm]![SubfrmAbsentDay].[Form]![txtTotalAbsent]
StrTotalDeductions = [Forms]![frmPayrollMainForm]![SubfrmDeductionProcedure].[Form]![txtTotalDeductions]
StrTotalOvertime = [Forms]![frmPayrollMainForm]![SubfrmOverTime].[Form]![txtTotalOverTime]
StrCashAdvance = [Forms]![frmPayrollMainForm]![SubfrmCashAdvance].[Form]![txtTotalCashAdvance]

StrSQL = "INSERT INTO tblPayroll SET PayrollYear = p0, PayrollMonth = p1, WorkedDays = p2, GrossSalary = p3, TotalAllowances = p4, TotalAbsentdays = p5, TotalDeductions = p6, TotalOvertime = p7, CashAdvance = p8 " & _
         "WHERE EmployeeID = p9;"
              
    With CurrentDb.CreateQueryDef(vbNullString, StrSQL)
        .Parameters("p0") = Me.cboYear             'Main form
        .Parameters("p1") = Me.CboMonth            'Main form
        .Parameters("p2") = Me.txtDayOfMonth       'Main form
        .Parameters("p3") = StrGrossSalary         '(Sub)form
        .Parameters("p4") = StrTotalAllowances     '(Sub)form
        .Parameters("p5") = StrTotalAbsentdays     '(Sub)form
        .Parameters("p6") = StrTotalDeductions     '(Sub)form
        .Parameters("p7") = StrTotalOvertime       '(Sub)form
        .Parameters("p8") = StrCashAdvance         '(Sub)form
        .Parameters("p9") = Me.txtEmployeeID       'Main form
        
        .Execute dbFailOnError
        
    End With
    
    MsgBox "Enter your Data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"
      
    DoCmd.GoToRecord , , acGoTo, CrId

Why I try to change previous "String Variable", because those 3 fields is from "mainform".. 
Still have "Run time error 3134" 
See attached current picture.

 

[smtazulislam]

Also I try to change "UPDATE INTO" , and "INSERT INTO"

 

[arnelgp]

add a Database variable:

If Me.Dirty Then Me.Dirty = False

Dim CrId As Long
Dim StrGrossSalary As Variant
Dim StrTotalAllowances As Variant
Dim StrTotalAbsentdays As Variant
Dim StrTotalDeductions As Variant
Dim StrTotalOvertime As Variant
Dim StrCashAdvance As Variant
Dim StrSQL As String

Dim db As DAO.Database
Set db = Currentdb
CrId = Me.CurrentRecord
StrGrossSalary = Me![SubfrmEmployeeSalaries][txtGS]
StrTotalAllowances = Me![SubfrmAllowanceData]![txtTotalAllowances]
StrTotalAbsentdays = Me![SubfrmAbsentDay]![txtTotalAbsent]
StrTotalDeductions = Me![SubfrmDeductionProcedure]![txtTotalDeductions]
StrTotalOvertime = Me![SubfrmOverTime]![txtTotalOverTime]
StrCashAdvance = Me![SubfrmCashAdvance]![txtTotalCashAdvance]

StrSQL = "INSERT INTO tblPayroll SET PayrollYear = p0, PayrollMonth = p1, WorkedDays = p2, GrossSalary = p3, TotalAllowances = p4, TotalAbsentdays = p5, TotalDeductions = p6, TotalOvertime = p7, CashAdvance = p8 " & _
         "WHERE EmployeeID = p9;"
              
    With Db.CreateQueryDef(vbNullString, StrSQL)
        .Parameters("p0") = Me.cboYear             'Main form
        .Parameters("p1") = Me.CboMonth            'Main form
        .Parameters("p2") = Me.txtDayOfMonth       'Main form
        .Parameters("p3") = StrGrossSalary         '(Sub)form
        .Parameters("p4") = StrTotalAllowances     '(Sub)form
        .Parameters("p5") = StrTotalAbsentdays     '(Sub)form
        .Parameters("p6") = StrTotalDeductions     '(Sub)form
        .Parameters("p7") = StrTotalOvertime       '(Sub)form
        .Parameters("p8") = StrCashAdvance         '(Sub)form
        .Parameters("p9") = Me.txtEmployeeID       'Main form
        
        .Execute dbFailOnError
        
    End With
    
    MsgBox "Enter your Data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"
      
    DoCmd.GoToRecord , , acGoTo, CrId

    Set db = Nothing

 

[NauticalGent]

Why would you want to have the same data in different tables? Is the data time-based? You really need to read up on normalization.

If you continue to ignore the advice given and insist on plugging in code you do not understand, you are doing yourself a HUGE disservice.

 

[Minty]

@arnelgp That syntax is off - you don't use a SET clause in an Insert query, only an Update?

It doesn't help that the OP seems to have switched them around at various points.

 

[NauticalGent]

@arnelgp That syntax is off - you don't use a SET clause in an Insert query, only an Update?

It doesn't help that the OP seems to have switched them around at various points.

Post # 6 confirms that the OP is trying to update one table with data that is in other tables: Not a good practice.

 

[smtazulislam]

Why would you want to have the same data in different tables? Is the data time-based? You really need to read up on normalization.

Thank you so much for your reply.
Thanks for your good adviced. I try is before but I can't get good result. Because, when I try to create a query from these tables, then I got NULL (no record). Because, some table is blank record, so that I dont get exactly NET SALARY frm query report as same month .
For that, I try to keep all of the data in a table for create salary Slip for each employee.

 

[smtazulislam]

add a Database variable:

If Me.Dirty Then Me.Dirty = False

Dim CrId As Long
Dim StrGrossSalary As Variant
Dim StrTotalAllowances As Variant
Dim StrTotalAbsentdays As Variant
Dim StrTotalDeductions As Variant
Dim StrTotalOvertime As Variant
Dim StrCashAdvance As Variant
Dim StrSQL As String

Dim db As DAO.Database
Set db = Currentdb
CrId = Me.CurrentRecord
StrGrossSalary = Me![SubfrmEmployeeSalaries][txtGS]
StrTotalAllowances = Me![SubfrmAllowanceData]![txtTotalAllowances]
StrTotalAbsentdays = Me![SubfrmAbsentDay]![txtTotalAbsent]
StrTotalDeductions = Me![SubfrmDeductionProcedure]![txtTotalDeductions]
StrTotalOvertime = Me![SubfrmOverTime]![txtTotalOverTime]
StrCashAdvance = Me![SubfrmCashAdvance]![txtTotalCashAdvance]

StrSQL = "INSERT INTO tblPayroll SET PayrollYear = p0, PayrollMonth = p1, WorkedDays = p2, GrossSalary = p3, TotalAllowances = p4, TotalAbsentdays = p5, TotalDeductions = p6, TotalOvertime = p7, CashAdvance = p8 " & _
         "WHERE EmployeeID = p9;"
             
    With Db.CreateQueryDef(vbNullString, StrSQL)
        .Parameters("p0") = Me.cboYear             'Main form
        .Parameters("p1") = Me.CboMonth            'Main form
        .Parameters("p2") = Me.txtDayOfMonth       'Main form
        .Parameters("p3") = StrGrossSalary         '(Sub)form
        .Parameters("p4") = StrTotalAllowances     '(Sub)form
        .Parameters("p5") = StrTotalAbsentdays     '(Sub)form
        .Parameters("p6") = StrTotalDeductions     '(Sub)form
        .Parameters("p7") = StrTotalOvertime       '(Sub)form
        .Parameters("p8") = StrCashAdvance         '(Sub)form
        .Parameters("p9") = Me.txtEmployeeID       'Main form
       
        .Execute dbFailOnError
       
    End With
   
    MsgBox "Enter your Data have been updated in the table ", vbInformation + vbOKOnly, "Update Info"
     
    DoCmd.GoToRecord , , acGoTo, CrId

    Set db = Nothing

Thank you so much for correction again,
I try it, but its also error.

 

[Minty]

All of this manoeuvring is completely unnecessary.

You have these numbers and therefore can easily create a query to sum them and display them, if any data changes your sums will always be correct - your separate table with the saved values won't be without running a load of updates to maintain it.

If you have null values you can NZ([YourSumField],0) and it will display a zero. Simple.

 

[smtazulislam]

All of this manoeuvring is completely unnecessary.

You have these numbers and therefore can easily create a query to sum them and display them, if any data changes your sums will always be correct - your separate table with the saved values won't be without running a load of updates to maintain it.

If you have null values you can NZ([YourSumField],0) and it will display a zero. Simple.

Thanks for your reply.
This formula I already applied in the first time. These sub(form) have "NZ()" and also field have default "0". This is no problem I created. But there have many duplicates data to an emloyee. and when I add the tblPayroll then result (no record)

 

[smtazulislam]

any help....

 

[WAB]

Sorry to say that... I have been programming a lot of Payrolls and it is sometimes hard to sort that all out - but I have identified for myself some points about structuring such data that actual works in all countries I have been working. From that point of view I would strongly recommend to normalize the data correctly (which seems not to be the case in your approach). I have run several times in the last 30 years into situations, where I deleted a lot of Forms and work to start from scratch to get it right - cause my experience is that is i compromise at one point with the data structure it keeps me haunting. One of the leading questions is always: How is the actual workflow BEFORE putting it in the system - how SHOULD it be optimized and processed within a system. In my early years i made some mistakes by trying to put 1 to 1 existing workflows in a database system - this never worked out for me - and I always tell my clients to look at the workflow FIRST - then optimize it - then program it.

I usually need only 2 tables: Employees - and the payroll. In the payroll form that is loaded every month from the data in the employees table i have all functions to calculate NET Salaries (with different Taxation approaches, National Security Fonds for Health, old age etc.).
To give only one example: You primary key in the payroll is wrong. If it is the fact that you have a monthly payroll - the primary key need to be the Employee-ID and the MONTH together.
In the employee table I have ALL information for processing "normal" monthly salaries - meaning:
Taxrates, Children/Spouses etc. In this case I have a tax-allowance BEFORE Tax and a special Tax allowance that is deducted from the calculated TAX.

ID Long Integer 4
active Yes/No 1

employment started Date With Time 8
prob_ends Date With Time 8
in_prob_period Yes/No 1

evaluationdate Date With Time 8
employment_ends Date With Time 8
Ename Short Text 25

Eprename Short Text 25
sex Short Text 1
birthday Date With Time 8

project Short Text 10
SocSec-number Short Text 255
idcard Short Text 20

EMAIL ADDRESS Short Text 255
PHONE NUMBER Short Text 255
CURRENT ADDRESS Short Text 255

startsalary Currency 8
next step raise Date With Time 8
current salary Currency 8

taxallowance_tdpt Currency 8
taxallowance_add Currency 8
comments Short Text 255

bankaccount Short Text 255
married Yes/No 1
mcount Long Integer 4

children Long Integer 4
repdate Date With Time 8
inputby Short Text 255

taxrate Long Integer 4
annual_leave Long Integer 4

Every month the CFO is preloading the DATA from the employees into the monthly payroll. Before doing this he need to check if the data is correct (may be new child... marriage... higher salary). By the way your table employeesSalaries is not needed - if you get it right with the payroll table this is just a view on the payroll itself (so no need storing the data double). Until the payroll is actually "run" he can roll back al data from the month and post it again - but it need to be sure that for one specific month only ONE payroll can exist.
After posting the primary data he need to put in some additions - may be we need to deduct 3 days for absence - or pay more for Sunday work (here 100%). Now I have to confess this is NOT stored in the database but coming from the project managers who have to sign for things like that. So the CFO processes some paperwork too at that point.
So we have additional allowances (are they taxed or not??) and paybacks of Salary Advances. Now Salary Advances are in a different table: EmployeeID, month (together primary key), WHY, WHEN_PAID, Amount, MonthlyRateToPayBack, paybackBy (either CASH then its NOT deducted from salary or SAL then it is a UPDATE query AFTER the payroll has been initiated.

About the inserting of DATA into a table: I have to confess that I am not one of the best programmer - what I usually do if my strings (docmd.runsql "SQLstr") do not work - I first build it up as a query in access until I get the desired data - THEN i change it to a INSERT statement - THEN I copy it into the VBA as a string. I always had problems with DATE formats - Microsoft made a really mess when they inserted the windows date format into access. At the beginning my SQL code crashed because my clients had different regional setUps. You either have to force a certain setup or build the code to force correct date conversions - whatever you find in a client PC.
I do not know if this at all helps, cause I think you invested a lot of time in the system already - so if I cause any anger I apologize.
Well.. I was quite angry on others in the past some times when I was told that my approach is not... lets say appropriate.
Greetings
WAB

 

[NauticalGent]

Well.. I was quite angry on others in the past some times when I was told that my approach is not... lets say appropriate.

Well said. I have been in that boat as well and often wondered "WHY won't they just answer my question instead of telling me about how my understanding of Access is terrible?"

But I heeded their advice and although I am not even close to being an expert, I'm not that bad...

 

[smtazulislam]

Sorry to say that... I have been programming a lot of Payrolls and it is sometimes hard to sort that all out - but I have identified for myself some points about structuring such data that actual works in all countries I have been working. From that point of view I would strongly recommend to normalize the data correctly (which seems not to be the case in your approach). I have run several times in the last 30 years into situations, where I deleted a lot of Forms and work to start from scratch to get it right - cause my experience is that is i compromise at one point with the data structure it keeps me haunting. One of the leading questions is always: How is the actual workflow BEFORE putting it in the system - how SHOULD it be optimized and processed within a system. In my early years i made some mistakes by trying to put 1 to 1 existing workflows in a database system - this never worked out for me - and I always tell my clients to look at the workflow FIRST - then optimize it - then program it.

I usually need only 2 tables: Employees - and the payroll. In the payroll form that is loaded every month from the data in the employees table i have all functions to calculate NET Salaries (with different Taxation approaches, National Security Fonds for Health, old age etc.).
To give only one example: You primary key in the payroll is wrong. If it is the fact that you have a monthly payroll - the primary key need to be the Employee-ID and the MONTH together.
In the employee table I have ALL information for processing "normal" monthly salaries - meaning:
Taxrates, Children/Spouses etc. In this case I have a tax-allowance BEFORE Tax and a special Tax allowance that is deducted from the calculated TAX.

Thank you so much for your advice. appreciate for expense more time for me.

Your adviced that I create two tables. here I have problem to keep all of data by date.
eg. tblEmployeeSalaries- SalaryID (PK), EmployeeID, DateFrom, DateTo, ContractTypeID, Basic, Food, Others, Increase, GrossSalary, Available (if more then one rows available), Note;
and eg. tblAllowanceData- AllowanceID (PK), EmployeeID, EffectiveDate (we have 7 dept. and dept manager give date which date to start and cancal), CancellationDate, AllowanceTypeID (we have 9 categories allowance), TotalA, Description, Active (if cancel)
and eg. tblDeduction; tblOvertime; tblAbsent; tblCashAdvance, tblRentRoom
So, that I can't put with the employee table at all data or using two tables.

We need to all of data in seperate insert in the table. Because. after 2 years finished contract some worker take work benefit money AND some worker take benefit money when she/he go to final exit (Resign). So that all of the Cut Salary calculation we have to showed to worker. Becuase some of worker have letter to deduction his/her absent from years Medical Leave. So we should have this absent with MC AND absent without MC THEN absent with Dudection from Yearly Medical Leave.
Its very defficult my job.
I have already a tblEmployees- where have all data as you refers in the above. And I have a field GrossSalary in the tblEmployee which fileds is update from tblEmployeeSalaries.

AND tblEmployeeSalaries- Here also we need to put INCREASE salary option. INCREASED also not same all of the employees. Some employee have 5% (New Employee) , 7%(2nd and 3rd Contrct.) 10%(6Years Up) on based by Basic Salary.

I created my all of the table follow in the normalization design, But lots of condition each table that some calculation is not possible showed by query. Should I use VBA. or extra text box in the form.

eg. table deduction will have to calculation automatically. I should used unbond -text box Or " Query " for get DaySalary.
Lot of thing I try it.

I have already a completed Payroll as I said my first msg #1. But this payroll work perfectly. But here have lots of "unbond -text box" for calculation from another Sub(form). So, I want to delete some textbox and create new query.

My current payroll is work and display result as well. But problem is for create a salarySlip by monthly. when I create the salaryslip then query will NO RECORD display. Because, some table is blank. If I create query then many deplicate data showed.

By a way I solve this problem. But its not good data design.
How I do it ? I create a subform from tblPayroll And call all of the "Total Amount by Bound Text Box" and put code in the after_Event. then Its all of data recored save in the tblPayroll.

But i dont like it. I guess, when I create a FrontEnd and BackEnd then maybe I facing problem to calculation field. Maybe it work well. I am not sure.

In the last I thinking that, If I used an updated sql then I have save time to entry data and I can delete more then 7 forms.
I dont know, you understand or NOT in my explain, Because English language is my optional language. Maybe I use some nations word. apologize for that.

If possible send to me a sample copy of your db, I want to some take idea. Please nock me (smtazulislam at Yahoo. com)
Thank you.

 

[WAB]

OK - so 2 points:
POINT1: Can you make a flow chart of the process leading to the monthly information (what is static - depending on the contract - and what has to be checked on every month? As we say "the devil is in the details..." and I need to understand when what information is accessible that leads to the payroll. Some points seem to be easy with lookup-tables (contracts: 1st 5%, 2nd 7% .. 3rd... etc.). This still would be a part of the employee table...

POINT2: You heard about the Pareto principle? It says the first 20% effort you put into something generates 80% of the result... and if you need 100% it will cost a lot of work... Meaning: sometimes its easier to go to the core issue and leave some factors outside (for the time being... like putting things on paper etc... and we discover later that there is no need to include them in programming - or: we do it and know we are heading for the the last 20% and this will cost time).

My main approach to simplify is: I have only the ACTUAL situation in the employee table. Once it changes i overwrite the old data and update it to the new situation. I don't care for the history here, cause the history is done by the payroll. You can make views on the payroll seeing for example: how many contracts did he have so far, how many years did he work, when did he get paid what... The salary slips are a report based on the payroll - and because all data is there you have no need to program a lot.

I will try to put that together in a test dba - give me some time...
By the way: Don't bother English is not my mother tongue either ( I was born in Germany...) - but live in Asia...

Because. after 2 years finished contract some worker take work benefit money AND some worker take benefit money when she/he go to final exit (Resign). So that all of the Cut Salary calculation we have to showed to worker. Becuase some of worker have letter to deduction his/her absent from years Medical Leave. So we should have this absent with MC AND absent without MC THEN absent with Dudection from Yearly Medical Leave.

I understand: You have a basic salary based on a contract - based on what kind of contract you have the employee gets some % PLUS on the basic salary. Before resigning there is some kind of benefit ( we call that severance pay - here its outside the tax -so I have taxable and non-taxable parts of the salary... ). I don't understand the "Letter of deduction": Does that mean the worker gets paid ALTHOUGH he was absent - and in some cases (when no letter is there) the days of absence get DEDUCTED from the gross salary?
Sorry... I still have more questions than answers..

 

[smtazulislam]

OK - so 2 points:
POINT1: Can you make a flow chart of the process leading to the monthly information (what is static - depending on the contract - and what has to be checked on every month? As we say "the devil is in the details..." and I need to understand when what information is accessible that leads to the payroll. Some points seem to be easy with lookup-tables (contracts: 1st 5%, 2nd 7% .. 3rd... etc.). This still would be a part of the employee table...

POINT2: You heard about the Pareto principle? It says the first 20% effort you put into something generates 80% of the result... and if you need 100% it will cost a lot of work... Meaning: sometimes its easier to go to the core issue and leave some factors outside (for the time being... like putting things on paper etc... and we discover later that there is no need to include them in programming - or: we do it and know we are heading for the the last 20% and this will cost time).

My main approach to simplify is: I have only the ACTUAL situation in the employee table. Once it changes i overwrite the old data and update it to the new situation. I don't care for the history here, cause the history is done by the payroll. You can make views on the payroll seeing for example: how many contracts did he have so far, how many years did he work, when did he get paid what... The salary slips are a report based on the payroll - and because all data is there you have no need to program a lot.

I will try to put that together in a test dba - give me some time...
By the way: Don't bother English is not my mother tongue either ( I was born in Germany...) - but live in Asia...


I understand: You have a basic salary based on a contract - based on what kind of contract you have the employee gets some % PLUS on the basic salary. Before resigning there is some kind of benefit ( we call that severance pay - here its outside the tax -so I have taxable and non-taxable parts of the salary... ). I don't understand the "Letter of deduction": Does that mean the worker gets paid ALTHOUGH he was absent - and in some cases (when no letter is there) the days of absence get DEDUCTED from the gross salary?
Sorry... I still have more questions than answers..

Actually, I should keep the history of the employee mainly with contractSalary, Absent, OverTime, and CashAdvance etc.
I forget to talking about "discipline ". if any worker avoid the discipline in the working time. then site engineer or Supervisor can deduct his salary with reason report. Then HR dept will reviews his "attitude + previous any misconduct" then they decide deduct or not deduct. This kinds of work, we called "DEDUCTION".

I appreciate to you for prepare a test db.
Also I request to you, give me your email (Public Or Personal messenger) to sending my db for reviews my table data. then you get little ideas which data exactly work on the based a table. then you can re-arrange data tables, to put with a table or many . Thank you.