Help me to generate Overtime Report

[prajithk.15]

Hi Guys,

I am new in MS access and i was told to generate an overtime report based on existing table which is made by my colleague 2 years ago.we are working 5 days @9 hrs per day OR 45 hours per WEEK.Through our timesheet filling application in access we are fill the details and it goes to a table(timesheetTable).people may book 9 hours on different project, i mean 3 hr on one project,2 hr on another project,.. and so on which will be a total of 9. If total for the day exceeds 9 rest will be treated as overtime. In the table there is no overtime field. The sample Timesheet table may look like this..

empcode project activity weekID Day TotalHours
100 proj1 drawing 29/2007 7/6/2007 4
100 proj1 engg 29/2007 7/6/2007 6
100 proj1 comms 29/2007 7/6/2007 3

100 proj2 plng 30/2007 7/9/2007 14

friday and saturday is off for us.

here i need to generate report which shows weekly overtime for the given project. i think i need to create a query which includes overtime filed calculated ( may be not ). here how can i generate grouped report of each dept and person which has exceeded 45 hours per week from the existing table?

I need a report like this (we can just enter the project Parameter, which calculate overtime consumed for that particular project. ieroj1)

Dept 1
empcode WeeKID Day1 Project1 Overtime1

Kiran 29/2007 7/6/2007 proj1 4
John 29/2007 7/6/2007 proj1 8
Kiran 30/2007 7/12/2007 proj1 5

Dept1 Total Total
Dept 2

kelvin 29/2007 7/6/2007 proj1 4
Das 29/2007 7/6/2007 proj1 8
alan 30/2007 7/12/2007 proj1 5

Dept2 Total Total

please advise me in details( with sample if possible)

Thanks in advance for your attempt,

Prajith

 

[WayneRyan]

Prajith,

This query will give you a list of all employees had days with
overtime.

Select empcode, Day, Sum(TotalHours)
From   Timesheet
Group By empcode, Day 
Having Sum(TotalHours) > 9

You could have a subsequent query retrieve the Department Number
(from the Employee table?).

The report can do the sorting/grouping/summing for you.

btw,

From the employee perspective, you can easily retrieve things like
the overtime hours, hours-per-project, etc.

From the project perspective, you can only get total hours. There
is no way to attribute overtime charges to a project ... unless you
count the case where a day's work with overtime is ALL on one
project.

Wayne

 

[prajithk.15]

where can i give this select querry

Wyane,

Thanks a lot for your reply. can u tell me where can i type this SQl? from the SQl View?

if an employee exceeds more than 45 per week for a particular project for particular week, that can be overtime for that week for that employee.

when we run the report it will ask to enter the project number and we get the report as i mentioned. that is what i am looking for. Is it possible with this table?

excuse if i am telling something nonsense!

prajith

Prajith,

This query will give you a list of all employees had days with
overtime.

Select empcode, Day, Sum(TotalHours)
From   Timesheet
Group By empcode, Day 
Having Sum(TotalHours) > 9

You could have a subsequent query retrieve the Department Number
(from the Employee table?).

The report can do the sorting/grouping/summing for you.

btw,

From the employee perspective, you can easily retrieve things like
the overtime hours, hours-per-project, etc.

From the project perspective, you can only get total hours. There
is no way to attribute overtime charges to a project ... unless you
count the case where a day's work with overtime is ALL on one
project.

Wayne

 

[Rabbie]

please advise me in details( with sample if possible)

Thanks in advance for your attempt,

Prajith

How much are you prepared to pay for this? And do you want the answer typed in for you?

 

[prajithk.15]

Share your knowledge to develop yourself

If you can share your knoeledge , you are very much appreciated.. thats the price i can give you bro.

Thanks,

How much are you prepared to pay for this? And do you want the answer typed in for you?

 

[marsha D. Carpenter]

Monthly for capturing the daily hours spent on each activity for each month

I need help! Do you have a monthly time sheet within your applicaiton?