Table design for calculatinn overtime?

[Randy]

What kind of table design should I think about for calculating employee
overtime? I am designing an office cost analysis db. Obviously part of
this cost is employee wages. I will be entering each employee's daily
hours, so hours over 40 during the week is overtime. I would like to have a
report that calculates this for me to help determine office
cost....Thanks...Randy

 

[Duane Hookom]

You could create a totals query that groups by Employee and Week and sums
hours. If the sum is greater than 40, the extra would be overtime.

SELECT EmployeeID, DateFormat([WorkDate],"yyyy-ww") as YrWk,
Sum([HoursWorked]) -40 as OverTime
Group By EmployeeID, DateFormat([WorkDate],"yyyy-ww")
Having Sum([HoursWorked]) >40;

 

[TC]

IMO you should start by desigining the tables & getting those right. If
the tables are right, the queries are straightforward to write. If the
tables are /not/ right, the queries will drive you mad, because they
will never work the way that you want.

In most of the timesheet systems that I've seen, there is a many to
many relationship between people & tasks. A person can work on many
tasks, and conversely, a task can be worked on by many people. So you
need a joining table in the middle. I call that table, "task
involvements".

So you could have something like this:

tblPerson
PersonID < PK
person name, DOB, etc.

tblTask
TaskID < PK
task name, created by, etc.

tblTaskInvolvement
TaskID } composite
PersonID } PK
involvement name or description
maybe involvement "type"
created by, created on, etc.

Then, it is simple to write a query to find all hours greater than 40
from any person in any given timesheet period.

HTH,
TC

 

[TC]

PS. The benefit of the structure I suggested, is that it tells you who
has word /on what/. So you could answer questions like:
- "Who worked on task 'X' & how long did each of them spend?"
- "What was the total time spent on task 'Y' this year?"
and so on.

HTH,
TC

 

[Sam]

I want to create a "timesheet" in Access. I want to enter the AM time in;
Out/lunch; In/lunch; Pm Out and total the hours by the day. I have created
the table and query. I want to setup the query to calculate the time and
then create a form based on the query to enter data. I need to know how to
code for calculating the time.
Thanks!
Sam

You could create a totals query that groups by Employee and Week and sums
hours. If the sum is greater than 40, the extra would be overtime.

SELECT EmployeeID, DateFormat([WorkDate],"yyyy-ww") as YrWk,
Sum([HoursWorked]) -40 as OverTime
Group By EmployeeID, DateFormat([WorkDate],"yyyy-ww")
Having Sum([HoursWorked]) >40;

--
Duane Hookom
MS Access MVP
--

What kind of table design should I think about for calculating employee
overtime? I am designing an office cost analysis db. Obviously part of
this cost is employee wages. I will be entering each employee's daily
hours, so hours over 40 during the week is overtime. I would like to have
a report that calculates this for me to help determine office
cost....Thanks...Randy

 

[Duane Hookom]

Open any module window and press [F1]. Look up information on Date functions
such as DateDiff() and DateAdd().

--
Duane Hookom
MS Access MVP
--

I want to create a "timesheet" in Access. I want to enter the AM time in;
Out/lunch; In/lunch; Pm Out and total the hours by the day. I have
created
the table and query. I want to setup the query to calculate the time and
then create a form based on the query to enter data. I need to know how
to
code for calculating the time.
Thanks!
Sam

You could create a totals query that groups by Employee and Week and sums
hours. If the sum is greater than 40, the extra would be overtime.

SELECT EmployeeID, DateFormat([WorkDate],"yyyy-ww") as YrWk,
Sum([HoursWorked]) -40 as OverTime
Group By EmployeeID, DateFormat([WorkDate],"yyyy-ww")
Having Sum([HoursWorked]) >40;

--
Duane Hookom
MS Access MVP
--

What kind of table design should I think about for calculating employee
overtime? I am designing an office cost analysis db. Obviously part
of
this cost is employee wages. I will be entering each employee's daily
hours, so hours over 40 during the week is overtime. I would like to
have
a report that calculates this for me to help determine office
cost....Thanks...Randy