Extending a Query to find additional data

[NigelC]

Hi All,

I have developed a report that runs a query to work out all the engineers wages for a particular week. I have a table called projects, all the hours that our engineers work on a particular project is recorded in a table called WorkSheets.

The Query asks for the date range and then the report details each engineer, where they worked, how many hours and then it calculates their wage, (via finding out the engineers hourly rate from the Staff table.

It works great and has increased accuracy and saves a lot of time.

However, there are flaws which I do not know how to tackle:

I have another table that records wages to be paid to our engineers that are not linked to a project. For example; this additional table records holiday, sick leave, and when I send engineers out to check a potentially new job, which I pay them for.

Question:

I can't get my head around how I should integrate this additonal table with my query/report. What I want is the report should show each days work by engineer, (as it already does), but then check the other table to see if they have been on holiday/sick leave etc, and add that to the engineers list of work. Then the calculations for their weekly salary will include any leave/sick.

At the moment I have cheated by making a project up called annual leave, and a project called sick leave and so on. However, this is not good as when I run a query for active projects it shows holiday and sick leave as a project.

Thanks

 

[jdraw]

Hi All,

I have developed a report that runs a query to work out all the engineers wages for a particular week. I have a table called projects, all the hours that our engineers work on a particular project is recorded in a table called WorkSheets.

The Query asks for the date range and then the report details each engineer, where they worked, how many hours and then it calculates their wage, (via finding out the engineers hourly rate from the Staff table.

It works great and has increased accuracy and saves a lot of time.

However, there are flaws which I do not know how to tackle:

I have another table that records wages to be paid to our engineers that are not linked to a project. For example; this additional table records holiday, sick leave, and when I send engineers out to check a potentially new job, which I pay them for.

Question:

I can't get my head around how I should integrate this additonal table with my query/report. What I want is the report should show each days work by engineer, (as it already does), but then check the other table to see if they have been on holiday/sick leave etc, and add that to the engineers list of work. Then the calculations for their weekly salary will include any leave/sick.

At the moment I have cheated by making a project up called annual leave, and a project called sick leave and so on. However, this is not good as when I run a query for active projects it shows holiday and sick leave as a project.

Thanks

Nigel,

My view is that you have much of the information. It's a matter of assembly of the pieces.
I think you have to determine what makes up "wages or pay".
For example:

Bob is working on ProjectX for 2 weeks(assume 10 workdays). During that period, he was actively working on ProjectX for 6 days, he had 1 sick day ,
2 days annual leave and 1 day investigating a new job.

So his Total pay for the period is

ProjectX related: 6 days * h hrs/day * $/hr
Sick leave : 1 day (sick rate???)
Annual leave : 2 days * (leave rate??)
NewJob related : 1 day * ( new job investigation rate)

Total pay seems to be sum of these minus relevant taxes, deductions...

You also have to consider company policies:
Does a salaried employee get full pay for sick days? How many sick days in a work period are considered normal? Different engineers may have different pay scales, annual leave credits and policies...
Just some thoughts.

 

[NigelC]

jdraw,

Your summation of what is required is absolutely correct. My problem is that I understand what I want, but the query/report finds its data from the project table that links to the worksheet table that is linked to the staff table.

Therefore all the engineers rates of pay is taken into account and the report is 100% accurate.

The problem is, the annual leave, sick pay and other payable items that is not related to a project is stored in an un-related table and I do not understand how to bring this additional data into my existing query, and still ordered by engineer.

Thanks for your input,

Nigel

 

[jdraw]

jdraw,

Your summation of what is required is absolutely correct. My problem is that I understand what I want, but the query/report finds its data from the project table that links to the worksheet table that is linked to the staff table.

Therefore all the engineers rates of pay is taken into account and the report is 100% accurate.

The problem is, the annual leave, sick pay and other payable items that is not related to a project is stored in an un-related table and I do not understand how to bring this additional data into my existing query, and still ordered by engineer.

Thanks for your input,

Nigel

I think there are 2 main players-- the person and time period involved.
Take a slightly different look at the problem.

The people (engineers) you are dealing with have things that occupy their time...(time occupiers).

Each time occupier has an "hourly rate" associated with it.
So the question becomes:
How was each engineers "time" occupied during the period in question.

It isn't only the project associated time that constitutes the total wage for the time period.

In my view you need to associate each of the "time occupier components", the number of hours of each and the associated rates for each engineer for the given time period.

You can't focus only on project time.

Hope this helps.

 

[NigelC]

jdraw,

Thanks, I think I understand what you are saying. I need to look the query from a different perspective.

I will review my logical data model and re-write my queries.

Nigel