Total of current salaries

[thezed]

Hi, I have an issue with a calculation I want to make. Let me explain.

I have a table 'Staff' with all the employees. Under each Record I have a DateJoined and a DateLeft (empty if currently employed). I also have a MonthlySalary field and a LastIncreaseDate and LastIncreaseAmount.

What I am trying to do on a Summary layout is to have the total salary paid per employee, and grand total, for the Current date. Where I get a bit confused is that there are a lot of parameters:

- if the employee joined after Jan 1st,

- if the employee left during the year

- if there is a salary increase during the year

All help will be welcomed!

Z.

[jamesducker]

Do you mean you'd like to pick any date, and find out what the total salary costs were on that date?

If so then you need another table of salaries.

First table = staff (fields: uniqueID, name, date joined*, date left*)

Second = salaries (fields: uniqueID, staffMemberID, salaryValue, dateSalaryStarted, dateSalaryEnded)

Create a relationship between them, from staff::uniqueID to salaries::staffMemberID, then put a portal on the staff layout showing records from that relationship.

What you then do is put every staff member's salary in to the portal, with the date that particular salary value came in to effect and the date it stopped.

For the time being, to keep things simple, if a salary is current, put in 31 December 09 or something as the end date.

Then we need to do things with global fields and relationships to work out each person's salary on a given date, and present them in a report.

That however is another job for another day... get the above sorted and then post again on this topic, I've subscribed to it so will see when you reply and we can go from there.

Hope that helps.

James

[thezed]

Thanks a lot James. THis is just the sparkle I needed, i think I understand how to go forward from there.

Thanks again for taking the time to help me!

And by the way, re Decent Group, you would not happen to have an office or a partnership with a company in Hong Kong would you?

Z.

[jamesducker]

No problem at all, hope it all works out for you, but if not then please reply again and we'll see what we can do.

We don't have offices anywhere apart from here in sunny England, although we do have clients in France, Greece, Belgium, NY and Libya. Nearly all of our work is done remotely nowadays so distance doesn't really matter.

Give me a shout if there's anything else we can help with.

Cheers

James

[thezed]

I will definitely contact you if I need anything. I actually have developed something myself just now and if this works out the way I want to I may have to have it redone in a more 'professional way'. Will let you know.

Thanks again for your help,

Z.