Solved

# getting payroll data

Posted on 2011-05-04
208 Views
I have a database with employee work hours.  They are eligible for extra pay on statutory holidays if they have worked 15 or more of the previous 30 days.  I have developed the code to query which employees are eligible, but i would like to also do the calculation to see how many hours they are to be paid for.  The result is the number of hours/days they have worked in the past 30.  (i.e. if they worked 110 hours in 16 days over the past 30 then the average would be 6.87 hours)

the table is named 'paydata' and the fields are EmpNumber (employee number), EmpName (employee name), Hours (hours worked each day) and WorkDate

``````SELECT paydata.EmpNumber, paydata.EmpName, Sum(paydata.Hours) AS SumOfHours
FROM paydata
WHERE (((paydata.WorkDate) Between [start] And [end]))
GROUP BY paydata.EmpNumber, paydata.EmpName
HAVING (((Count(paydata.hours))>[enter threshold]))
ORDER BY paydata.EmpName;
``````
0
Question by:Gerw

LVL 44

Expert Comment

So what do you do with an average of 6.87 hrs/day?  It would be nice to know if the extra pay is a percentage.  Do you have a tblHolidays?  Is paydata multi-year?

0

Author Comment

I calculate this for each statutory holiday.  For example, the next stat holiday is July 1st for us, so I will do an inquiry asking for the number of days worked between June 1st and June 30th with a 'threshold' of 14.  Anyone with 15 days or more will show up in the inquiry together with their total hours.

The addition to the inquiry which I am trying to build would calculate the average hours which gets entered into the payroll as 'stat hours' and the payroll system (totally separate) calculates their pay owing based on the normal rate of pay.

There is no tblHolidays, although it might be a nice feature.  We have 10 stat holidays a year here in Canada so it is not an onerous job to do the inquiry.  If we had such a table, the inquiry could check to see if a holiday falls within a pay period and then does the 30 day calculation and separates the overtime pay for anyone eligible who happened to work on the stat holiday.  (our business is a restaurant and we are open seven days a week.)

Just to be clear - to be eligible for stat holiday pay or overtime pay, the employee must have worked 15 of the previous 30 days prior to the holiday and of course overtime is only paid if the employee worked on the holiday and was eligible.
0

LVL 44

Accepted Solution

Then you need a PayFactor field.  I used 1.5, and 1.0 - you should use what ever your situation dictates.

``````SELECT paydata.EmpNumber, paydata.EmpName,
Sum(paydata.Hours) AS SumOfHours,
Iif(Count(paydata.hours))>[enter threshold]),1.5,1.0) as PayFactor
FROM paydata
WHERE (((paydata.WorkDate) Between [start] And [end]))
GROUP BY paydata.EmpNumber, paydata.EmpName
HAVING (((Count(paydata.hours))>[enter threshold]))
ORDER BY paydata.EmpName;
``````
0

Author Comment

Sorry, I don't understand the 1.5 and 1.0 PayFactor.  What I need is for the query to divide the total hours worked in the past 30 days by the number of days the employee worked so long as it was greater than 14.
0

LVL 44

Expert Comment

And when you have an employee who was sick for the past 30 days, that logic generates a divide-by-zero error.  I still do not grasp where you are coming from - and I designed the system that billed the prime over \$140M over 15 years.  Rather than specifics, can you 'tell' us what happens, and what you need?
0

Assisted Solution

GrayL - I have managed to come up with the following based on your response.  Using your structure and applying it to what I needed here is my solution

SELECT paydata.EmpNumber, paydata.EmpName, Sum(paydata.Hours) AS SumOfHours, Avg(paydata.Hours) AS AverageHours
FROM paydata
WHERE (((paydata.WorkDate) Between [start] And [end]))
GROUP BY paydata.EmpNumber, paydata.EmpName
HAVING (((Count(paydata.hours))>10))
ORDER BY paydata.EmpName;

I am temporarily using the number 10 for the number of days for the cutoff, but I will change that to 14 in the final version.  Thanks for the help.  ( this all had to be rebuilt after a crash caused by an external hard drive which contained my backup files.  a bizarre event)  I will pursue the idea of a tblStatHolidays that you asked about.  ideally the query would check for a Stat Holiday date and if it found one, would count back 30 days to determine who is eligible for stat pay or overtime pay.  Then it would separate the overtime hours for that day for eligible employees and provide the average hours as above for those employees who are eligible for stat holiday pay.
0

Author Comment

I meant to award the 500 points to GrayL for his help in arriving at my solutilon.
0

LVL 44

Expert Comment

Thanks, I think?
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

#### 794 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!