Data from another table (1 Viewer)

[chrismusic79]

Hi,

Hopefully this is a simple problem, that has a simple answer.

I am creating a payroll database for the company I work for and have not been able to get around this problem. I normally use excel, but my boss wants something a bit more secure.

I have a table detailing all employee's (Name, ID, etc and Tax Code) I also have another table with the weekly hours worked, but I need to have the tax code in that table. The problem is that the tax code can change throughout the year and therefore change my calculations (in the weekly hours table).

is there a way of copying the "Tax Code" from the Employee table to the Weekly Hours table each time a new record is created.

Thanks for all you help

 

[smig]

The tax code should be in neighter of these table. It should be in another one that will hold these details - workerID, TaxCode, StartDate.

 

[chrismusic79]

Thanks for the reply. I would agree, but when I change it in the new table, would it not still change all the entries in the weekly hours.

 

[gemma-the-husky]

seriously - do not try and build a payroll system, anyway.

I hope this is not too patronising, but if you need to ask how to do something as relatively simple as managing tax codes, then I doubt very much if you will be able to complete the project.

It's not just a tax code, though. it's tax code changes. cumulative/week1 payroll, tax free pay, varying tax rates, maximum deductions, P45's and other P forms.

NI, pension, other stoppages. NI contracted in, contracted out. Special rules for directors. Employer's contributions as well as employee's contributions,

Year end summaries. P35's P60's, P11D's.

etc etc

Just buy a system, or use a payroll bureau.

Are you in the UK? Did you realise you HAVE to process all payroll events online. That's enough to put most people off. If you are small enough, you can use the free HMRC small employer payroll.

 

[chrismusic79]

H Gemma

Sorr y that doesn't help. I'm not in the UK and I know all about the tax system in my county.

Any chance you could just help.

 

[gemma-the-husky]

taxcode = dlookup("taxcode","employeetable","employeeid = " & employeeid)

 

[spikepl]

The problem is that the tax code can change throughout the year and therefore change my calculations (in the weekly hours table).

is there a way of copying the "Tax Code" from the Employee table to the Weekly Hours table each time a new record is created.

1. Dave is right - developing is much more costly than you think. And reinventing the wheel - developing something that thousands of other companies simply buy - makes little sense.

2. There are two main ways of dealing with time-changing data.

First, one like outlined in #2, so for each calculation you find the taxcode valid for the specific date.

Second: for each calculation you store the taxcode and whatever else variable values there are, together with the result. So the invoice contains a copy of prices and taxes valid at the time of issue. That prevents the invoice being changed by subsequent price or tax changes.

 

[gemma-the-husky]

chris

if you already do exactly this in a spreadsheet, and want to convert the functionality to a database, then it is doable.

One important difference between a spreadsheet and a database is that with a dbs you rarely see all the data. If you currently use a spreadsheet, then no doubt you have a separate weekly/monthly spreadsheet for each payroll week, clone last weeks to give you this weeks, and so on. Importantly you can visually inspect all the data for errors, and so on.

a dbs would only have a single table (set of tables) - ie not separate tables for each week - and you would filter/report the data by extracting a selection of data. Because of this, errors can easily propagate without you being aware of them at all, and with payroll the onus is on the employer most of the time (not sure where you are based). The trick/aim of a database is to manage the data so that data cannot go astray, without the user needing to manually check things.

so it's doable, but managing a database needs quite a different set of skills to managing a spreadsheet.

It's hard to see how the development cost of doing a payroll yourself can be less than buying a ready made package, added to which there is the underlying problem of errors within the app.

Good luck, anyway.

 

[Solo712]

Hi,

Hopefully this is a simple problem, that has a simple answer.

I am creating a payroll database for the company I work for and have not been able to get around this problem. I normally use excel, but my boss wants something a bit more secure.

I have a table detailing all employee's (Name, ID, etc and Tax Code) I also have another table with the weekly hours worked, but I need to have the tax code in that table. The problem is that the tax code can change throughout the year and therefore change my calculations (in the weekly hours table).

is there a way of copying the "Tax Code" from the Employee table to the Weekly Hours table each time a new record is created.

Thanks for all you help

It is a simple problem. What changes are the rates for different tax codes in the weekly payroll. Therefore you need a table connecting tax codes to tax code rates for given time periods. So when doing a pay period you would simply use the info from the employee table (tax code) and the lookup from tax rates table (based on a date range when these codes are valid) to calculate the tax withheld. Simple enough ? The tax rates table records would look sth like this:

ID
Start Date
End Date
Tax Code A Rate
Tax Code B Rate
.....etc

Hope this helps.

Best,
Jiri

 

[accedeholdings]

Solo 712 your solution rocks the most. It is simple yet easy to implement. Thank you.

 

[Solo712]

Solo 712 your solution rocks the most. It is simple yet easy to implement. Thank you.

You are welcome !