payroll query - not sure how to approach it. check if rep worked holidays.

[betterthan74]

I need Ideas on how to approach a query in access 2007. Its to figure bonus for working particular days I have two tables that are imported from different software One table is daily and lists each employee ID, their part/full-time/oncall code status and hours worked each day Date | ID - | jobcode | hours worked | 8/01/2012 | 003 | Full | 8 | 8/01/2012 | 004 | Part | 4 | 8/02/2012 | 003 | Full | 8 | 8/02/2012 | 004 | part | 5 | My second table has a holiday beginning and end day and how many total hours each type of employee must work those days or range of days begin | End | Parttime | Fulltime | 8/01/2012 | 8/01/2012 | 4 | 8 | 9/01/2012 | 9/02/2012 | 4 | 12 | 10/01/2012 | 8/01/2012 | 4 | 8 | 11/01/2012 | 8/01/2012 | 4 | 8 | There are about 30 holidays in a year and if an employee works the required hours they get a bonus after one year. I need to create a query that Tells me the holidays the employess did not meet their requiement and how much they were off by. I've thought of doing it in vba where I am more comfortable with the language. is that the better route? Thanks for any ideas.

[June7]

How can you have 3 holidays on the same date?

Start with this query that will show Daily data if date is a holiday:
SELECT Daily.ID, Daily.Date, Daily.jobcode, Daily.[Hours Worked], IIf([jobcode]="Part",[Parttime],[Fulltime]) AS HoursNeeded
FROM Daily INNER JOIN Holidays ON Daily.Date = Holidays.Begin;

Note the calculated field that determines how many hours needed.

[betterthan74]

The three holidays on one day was a typo. I've corrected it. Thanks for the call out.

[June7]

Did you try my query idea? One thing that might make it not workable is holiday with 12 hours crossing midnight. This could mean VBA will be needed.

[betterthan74]

I am working on it now.. I actually have 16 job codes, not just 2 and think a case statement might work since each rep can only have one job code on any given year The join was exactly what I was looking for, but I'm not sure how it works out if the rep worked all their required hours on the second day for the holidays that have 2 days. As long as they meet the total requirement it doesn't matter. I tried to use two joins but keep creating an ambigious join that way. Your SQl did make me understand that I need to include in my final report all of the holidays from the holidays table and then only the dates that the rep worked from the staffing table that match either the beginning or end date of the holiday. I wrecked the formatting of my answer when I went to edit it. Let me know if there is a way to restore it.

[June7]

Not important now but could maybe fix the post in the Advanced post editor. There is a tables tool bar.