Hi,
I'm building a database for HR in my company - the idea is to produce a report to show what is being paid out by payroll. There are 3 pay cycles - weekly, bi-weekly and monthly. The report will be grouped by the into these 3 categories.
I have created the report that does this for the weekly and bi-weekly employees. (I created one query for weekly and another for biweekly and unioned the two). The only thing is in the bi-weekly section employees appear twice.
e.g.
Bi-Weekly
John Smith Electrician 8/9/2006 €700
John Smith Electrician 1/9/2006 €600
What I want is to have John Smith appear once with the latest weekending date and the total he was paid.
I will need to do something similar with the monthly people. With them I have a calandar showing the dates they will be paid - it caries as there are 4 or 5 weeks in a month. However the month always ends on a friday.
Here is what I have so far. (The M_CandidatePostDetails holds the details of a particular job and candidate e.g. when they started it, where it is etc. The pay rates for that candidate in that position are in the M_PayRates table. )
I've created a table called L_PayCycle:
WeekEndDate PayCycle
1/9/06 BiWeekly1
9/9/06 BiWeekly1
I can get it to group it on those and then use this query in my main BiWeekly pay query so that it groups them on that.
However it seems to me that this will make the final union query very complicated - and slow to run. Also it means that the user has to enter the week end dates for the year and group them into 2 week periods with a made -up name (e.g. Biweekly1) in order to achieve this. Is there a simpler/better way to do it?
I'm building a database for HR in my company - the idea is to produce a report to show what is being paid out by payroll. There are 3 pay cycles - weekly, bi-weekly and monthly. The report will be grouped by the into these 3 categories.
I have created the report that does this for the weekly and bi-weekly employees. (I created one query for weekly and another for biweekly and unioned the two). The only thing is in the bi-weekly section employees appear twice.
e.g.
Bi-Weekly
John Smith Electrician 8/9/2006 €700
John Smith Electrician 1/9/2006 €600
What I want is to have John Smith appear once with the latest weekending date and the total he was paid.
I will need to do something similar with the monthly people. With them I have a calandar showing the dates they will be paid - it caries as there are 4 or 5 weeks in a month. However the month always ends on a friday.
Here is what I have so far. (The M_CandidatePostDetails holds the details of a particular job and candidate e.g. when they started it, where it is etc. The pay rates for that candidate in that position are in the M_PayRates table. )
I've created a table called L_PayCycle:
WeekEndDate PayCycle
1/9/06 BiWeekly1
9/9/06 BiWeekly1
I can get it to group it on those and then use this query in my main BiWeekly pay query so that it groups them on that.
However it seems to me that this will make the final union query very complicated - and slow to run. Also it means that the user has to enter the week end dates for the year and group them into 2 week periods with a made -up name (e.g. Biweekly1) in order to achieve this. Is there a simpler/better way to do it?
