Difficult to help you, as whatever you've got, I'm afraid to say it is not a relational database, and I am unfamiliar with your tax system, so all I can do is suggest some general pointers.
Firstly you need a table Years
-
TblYears
-
YearID AutoNumber Key
-
YearName Text
-
In the UK the current tax year is 2016/17 which is why I suggest text and note that "Year" is a reserved word and should be avoided.
I also suggest you don't use "ID" as the key to a table, something more meaningful like "TaxExemptID" will be much clearer.
So now you change your TblTaxRates and replace iFinancialYear with YearID and iDescription with TaxExemptID. Set up relationships between these 3 tables and enforce referential integrity.
You can then create a query using these 3 tables that will look very similar to your existing TblTaxRates but allows easy sorting and filtering.
Now in the UK tax is worked out on a person has some sort of code and you see how much they are paid in a week and look up in a table they get charged different tax rates depending on their earnings. It appears from your samples that something similar is happening but in your case a coefficient is deducted.
Therefor in your Employee table you need a pointer to the tax band that is applied and I am guessing this will be the TaxExemptID, so again set up a relationship between the Employee's TaxExemptID and the TaxExemptID in the TblTaxExempt.
There may be a complication of the TaxExemptID changing from year to year, but we will deal with that later.
I suspect your TblTaxCalculation should have an EmployeeID in it to link it with your Employee Table (Set up a relationship) and also some form of date or week number. Then you have a complete record of each employee, what they were paid each week / fortnight, and you can re-create the tax calculation.
Let me know howthis sounds
Phil
83
