RCurtin
09-12-2006, 05:11 AM
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.
http://geocities.com/roseanne_ie/GroupByQuery.JPG
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.
http://geocities.com/roseanne_ie/GroupByQuery.JPG
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?