What is equivalent to DLookup.... in Access?

**PhilOfWalton** · Dec 26 '16, 09:20 AM

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

Code:

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 "TaxExemptI D" 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 TblTaxCalculati on 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

What is equivalent to DLookup.... in Access?

What is equivalent to DLookup.... in Access?

Comment