Calculating Overtime in Access

[Guest]

I am trying to create an Access database to record our employee timecards and
compute wages and overtime.

Our timesheets are a little confusing because we are a tour company and our
guides and work 1, 2, or 3 tours per day, with time off in between. So I've
set up my timesheet database so that I have three sign-in and sign-out times.
Any times between would be unpaid time (similar to a lunch break).

So far, I've got an employees table and a timesheet table and I can enter
timesheet date in a form based on the timesheet table and then display all
hours for a particular employee using a form with subform. So far, so good
with that.

In my subform, I have a calculated field that used the HoursAndMinutes
function that works fine (I found the HoursAndMinutes example on the MS
office website).

My function is:

=HoursAndMinutes(([TimeOut1]-[TimeIn1])+IIf(IsNull([TimeOut2]-[TimeIn2]),0,[TimeOut2]-[TimeIn2])+IIf(IsNull([TimeOut3]-[TimeIn3]),0,[TimeOut3]-[TimeIn3]))

The field that does this computation is called 'Total'.

That gives me an accurate total of the hours and minutes an employee worked
on a given day.

Here's my problem:

Now I want to evaluate the Total field in a new field I've called RegHours
so see if it equals more than 10 hours in a day (we use flex time, so
anything over 10 hours / day is overtime). If Total is greater than 10, then
I want to enter 10 in RegHours and then the different in OT Hours. But
nothing I've tried will work. Everything returns an error unless I do
something really basic like try:

=[Total]

That returns the value I computed in the Total field. So I know my variable
name is okay, but I can't do anything with this.

I suspect is my have something to do with the fact I'm working with Times?

Can anyone help? I can do this fine in Excel, but can't figure out for the
life of me how to do this in Access.

I tried variations on:

= IIf([Total]>10,10,[Total])

But anything similar to this returns an error. I've also tried
Min(10,[Total]) which also returns an error.

Can anyone help?

 

[Allen Browne]

This question is probably bigger than can be resolved in the newsgroups, but
I'll try to give you a lead.

If you already have fields named RegHours and OTHours, it would be possible
to use an Update query to write the calculations to these fields. In query
design, choose Update on Query menu. Use an IIf() expression around your
HoursAndMinutes() function to test if the result > 10. If you get errors,
make sure you specify a return type for the HoursAndMinutes funtion, e.g.:
Function HoursAndMinutes(...) As Date

However, a more serious issue is that your approach is set up like a
spreadsheet and not like a database. An employee can have one or more logins
per date, and each one should be a different *record* instead of different
fields. Further, the totals for the date should then be different records in
a Pay table.

Suggested tables:
Employee table (one record per person):
EmployeeID AutoNumber primary key
Surname ...

Work table (one record for every tour):
WorkID AutoNumber primary key
EmployeeID Number (Long) foreign key to Employee.EmployeeID
WorkDate Date/Time date this shift belongs to.
StartTime Date/Time time this person started this
shift.
Minutes Long duration worked (in
minutes)

Pay table (one record for each employee for each week?):
PayID AutoNumber primary key
EmployeeID Number (Long foreign key to Employee.EmployeeID
PaidDate Date/Time date the payment was given to
employee.

PayDetail table (one record for each line item of the pay):
PayDetailID AutoNumber primary key
PayID Number (long) which pay this entry belongs
to
WorkDate Date/Time which date this entry is for.
Minutes Long number of minutes
PayPerHour Currency dollars per hour.

With this approach, the Work table gets filled in at the end of each tour.
If the person does 2 tours, they have 2 entries for the date. Four tours
would be 4 entries. You could use an EndTime, but storing the whole number
of minutes will give the most efficient calculations, and is logically
correct (duration is independent of starttime, and prevents problems if
shifts go past midnight).

At the end of the period (week?), you then calculate the pays for the
period. You create a single header record for each employee, and then the
detail records. Using a Totals query into the Work table, you Group By
EmployeeID and WorkDate, and Sum the Minutes. If the employee had more than
600 (10 hours) on one date, you create 2 entries for that date, e.g. 600
minutes @ $10/hr, and 30 minutes @ $15/hr.

In practice, you would probably write code to populate the Pay and PayDetail
tables at the click of a button. You would probably add a PayID field to the
Work table to track which Work records are included in which Pays, so it is
really easy to see which Work records you need to pick up for the pays. You
would also design the interface so that Work records cannot be edited or
deleted once the pays have been calculated.

That might be a bit more than you anticipated, but that's how a well
designed database would work for the situation you describe.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create an Access database to record our employee timecards
and
compute wages and overtime.

Our timesheets are a little confusing because we are a tour company and
our
guides and work 1, 2, or 3 tours per day, with time off in between. So
I've
set up my timesheet database so that I have three sign-in and sign-out
times.
Any times between would be unpaid time (similar to a lunch break).

So far, I've got an employees table and a timesheet table and I can enter
timesheet date in a form based on the timesheet table and then display all
hours for a particular employee using a form with subform. So far, so
good
with that.

In my subform, I have a calculated field that used the HoursAndMinutes
function that works fine (I found the HoursAndMinutes example on the MS
office website).

My function is:

=HoursAndMinutes(([TimeOut1]-[TimeIn1])+IIf(IsNull([TimeOut2]-[TimeIn2]),0,[TimeOut2]-[TimeIn2])+IIf(IsNull([TimeOut3]-[TimeIn3]),0,[TimeOut3]-[TimeIn3]))

The field that does this computation is called 'Total'.

That gives me an accurate total of the hours and minutes an employee
worked
on a given day.

Here's my problem:

Now I want to evaluate the Total field in a new field I've called RegHours
so see if it equals more than 10 hours in a day (we use flex time, so
anything over 10 hours / day is overtime). If Total is greater than 10,
then
I want to enter 10 in RegHours and then the different in OT Hours. But
nothing I've tried will work. Everything returns an error unless I do
something really basic like try:

=[Total]

That returns the value I computed in the Total field. So I know my
variable
name is okay, but I can't do anything with this.

I suspect is my have something to do with the fact I'm working with Times?

Can anyone help? I can do this fine in Excel, but can't figure out for
the
life of me how to do this in Access.

I tried variations on:

= IIf([Total]>10,10,[Total])

But anything similar to this returns an error. I've also tried
Min(10,[Total]) which also returns an error.

Can anyone help?

 

[Guest]

Thanks Allen. Your suggestions are right on target. I've restructured my
database so that there is one record for each tour. I haven't quite figured
out the Pay aspects, yet, but I think that will come.

Right now, my biggest challenge is to get a report I can use to calculate
payroll this week!

I am hung up on computing regular and overtime hours each day.

Our company operates on flextime -- so anything over 10 hours in a day is
paid at time and a half.

I've created a report that shows me:

WorkDate, StartTime, & EndTime for each tour worked.

These data are grouped by: Employee then by WorkDate, then by EventType

(Our guides can either work tours or events. We have to keep these separate
for reporting purposes. So for each tour, we designate whether it is a tour
or an event).

That is all working fine.

In my report, I am now trying to compute total hours, regular hours, and OT
hours.

I can get total hours okay. I've tried two approaches to doing that
computation. The first is to use the HoursAndMinutes function found on this
website which returns a string value when you compute EndTime - StartTime.
The variable I created to do this computation is called Total.

I've also tried using

=Sum([EndTime]-[StartTime])*24

This variable was named TotHours

I like how the answer comes out using this method better because it gives me
hours and part of hours the way my payroll company wants to see them. So
that is good.

Where I am hung up, however, is in computing OT and regular hours. Anytime
I try to do a computation using either Total or TotHours I get an error
message.

For instance, I tried:

=[TotHours]-10

I'm trying to get OT computed. Ideally, I want something like:

IIF(TotHours>10,10,TotHours)

But that returns and error. So I figured I'd start with something really
simple like =[TotHours]-10, but this returns an #Error answer for all
employees.

I thought maybe my problem had to do with it not treating TotHours as a
number, so I formated it as a general number. Same error.

Does anyone have any ideas about how to handle?

Kim

This question is probably bigger than can be resolved in the newsgroups, but
I'll try to give you a lead.

If you already have fields named RegHours and OTHours, it would be possible
to use an Update query to write the calculations to these fields. In query
design, choose Update on Query menu. Use an IIf() expression around your
HoursAndMinutes() function to test if the result > 10. If you get errors,
make sure you specify a return type for the HoursAndMinutes funtion, e.g.:
Function HoursAndMinutes(...) As Date

However, a more serious issue is that your approach is set up like a
spreadsheet and not like a database. An employee can have one or more logins
per date, and each one should be a different *record* instead of different
fields. Further, the totals for the date should then be different records in
a Pay table.

Suggested tables:
Employee table (one record per person):
EmployeeID AutoNumber primary key
Surname ...

Work table (one record for every tour):
WorkID AutoNumber primary key
EmployeeID Number (Long) foreign key to Employee.EmployeeID
WorkDate Date/Time date this shift belongs to.
StartTime Date/Time time this person started this
shift.
Minutes Long duration worked (in
minutes)

Pay table (one record for each employee for each week?):
PayID AutoNumber primary key
EmployeeID Number (Long foreign key to Employee.EmployeeID
PaidDate Date/Time date the payment was given to
employee.

PayDetail table (one record for each line item of the pay):
PayDetailID AutoNumber primary key
PayID Number (long) which pay this entry belongs
to
WorkDate Date/Time which date this entry is for.
Minutes Long number of minutes
PayPerHour Currency dollars per hour.

With this approach, the Work table gets filled in at the end of each tour.
If the person does 2 tours, they have 2 entries for the date. Four tours
would be 4 entries. You could use an EndTime, but storing the whole number
of minutes will give the most efficient calculations, and is logically
correct (duration is independent of starttime, and prevents problems if
shifts go past midnight).

At the end of the period (week?), you then calculate the pays for the
period. You create a single header record for each employee, and then the
detail records. Using a Totals query into the Work table, you Group By
EmployeeID and WorkDate, and Sum the Minutes. If the employee had more than
600 (10 hours) on one date, you create 2 entries for that date, e.g. 600
minutes @ $10/hr, and 30 minutes @ $15/hr.

In practice, you would probably write code to populate the Pay and PayDetail
tables at the click of a button. You would probably add a PayID field to the
Work table to track which Work records are included in which Pays, so it is
really easy to see which Work records you need to pick up for the pays. You
would also design the interface so that Work records cannot be edited or
deleted once the pays have been calculated.

That might be a bit more than you anticipated, but that's how a well
designed database would work for the situation you describe.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to create an Access database to record our employee timecards
and
compute wages and overtime.

Our timesheets are a little confusing because we are a tour company and
our
guides and work 1, 2, or 3 tours per day, with time off in between. So
I've
set up my timesheet database so that I have three sign-in and sign-out
times.
Any times between would be unpaid time (similar to a lunch break).

So far, I've got an employees table and a timesheet table and I can enter
timesheet date in a form based on the timesheet table and then display all
hours for a particular employee using a form with subform. So far, so
good
with that.

In my subform, I have a calculated field that used the HoursAndMinutes
function that works fine (I found the HoursAndMinutes example on the MS
office website).

My function is:

=HoursAndMinutes(([TimeOut1]-[TimeIn1])+IIf(IsNull([TimeOut2]-[TimeIn2]),0,[TimeOut2]-[TimeIn2])+IIf(IsNull([TimeOut3]-[TimeIn3]),0,[TimeOut3]-[TimeIn3]))

The field that does this computation is called 'Total'.

That gives me an accurate total of the hours and minutes an employee
worked
on a given day.

Here's my problem:

Now I want to evaluate the Total field in a new field I've called RegHours
so see if it equals more than 10 hours in a day (we use flex time, so
anything over 10 hours / day is overtime). If Total is greater than 10,
then
I want to enter 10 in RegHours and then the different in OT Hours. But
nothing I've tried will work. Everything returns an error unless I do
something really basic like try:

=[Total]

That returns the value I computed in the Total field. So I know my
variable
name is okay, but I can't do anything with this.

I suspect is my have something to do with the fact I'm working with Times?

Can anyone help? I can do this fine in Excel, but can't figure out for
the
life of me how to do this in Access.

I tried variations on:

= IIf([Total]>10,10,[Total])

But anything similar to this returns an error. I've also tried
Min(10,[Total]) which also returns an error.

Can anyone help?

 

[Allen Browne]

Okay, that's great. You have the structure, and the GROUP BY query.

For calculating pay rates, I would imagine it would be useful to have hours
and fractions of an hour, e.g. 10.5 hours = ten and a half hours.
Personally, I work in minutes, and convert the results to hours (divide by
60) at the end.

The expressions to type into the Field row of your query will be something
like this:

Minutes: DateDiff("n", [StartTime], [EndTime])

OverTime: IIf(DateDiff("n", [StartTime], [EndTime]) > 600, _
DateDiff("n", [StartTime], [EndTime]) - 600)

Regular: DateDiff("n", [StartTime], [EndTime]) Mod 600

It may be possible to work with your existing expressions by wrapping
CVDate() around your expressions to force them to the date/time data type,
but I think that's less desirable than working in minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. Your suggestions are right on target. I've restructured my
database so that there is one record for each tour. I haven't quite
figured
out the Pay aspects, yet, but I think that will come.

Right now, my biggest challenge is to get a report I can use to calculate
payroll this week!

I am hung up on computing regular and overtime hours each day.

Our company operates on flextime -- so anything over 10 hours in a day is
paid at time and a half.

I've created a report that shows me:

WorkDate, StartTime, & EndTime for each tour worked.

These data are grouped by: Employee then by WorkDate, then by EventType

(Our guides can either work tours or events. We have to keep these
separate
for reporting purposes. So for each tour, we designate whether it is a
tour
or an event).

That is all working fine.

In my report, I am now trying to compute total hours, regular hours, and
OT
hours.

I can get total hours okay. I've tried two approaches to doing that
computation. The first is to use the HoursAndMinutes function found on
this
website which returns a string value when you compute EndTime - StartTime.
The variable I created to do this computation is called Total.

I've also tried using

=Sum([EndTime]-[StartTime])*24

This variable was named TotHours

I like how the answer comes out using this method better because it gives
me
hours and part of hours the way my payroll company wants to see them. So
that is good.

Where I am hung up, however, is in computing OT and regular hours.
Anytime
I try to do a computation using either Total or TotHours I get an error
message.

For instance, I tried:

=[TotHours]-10

I'm trying to get OT computed. Ideally, I want something like:

IIF(TotHours>10,10,TotHours)

But that returns and error. So I figured I'd start with something really
simple like =[TotHours]-10, but this returns an #Error answer for all
employees.

I thought maybe my problem had to do with it not treating TotHours as a
number, so I formated it as a general number. Same error.

Does anyone have any ideas about how to handle?

Kim

 

[Guest]

Thanks Again, Allen. I've changed everything to match what you suggested and
the time functions are all working well now.

But, of course, I now have a new challenge. Sorry for the newbie questions,
but I'm not nearly as familiar with Access as I am Excel. However, Access
seems to be the better choice for this project so I'm trying to educate
myself!

Anyways, here's the new challenge...

What I have so far is a query and report that gives me a one page summary
for each employee showing me:

WorkDate, StartTime, EndTime, TotalHours, Type (event or tour), RegHours,
OTHours, and Gratuities.

What this gives is a one line summary for each tour ore event my guides
worked. However, this is not really what I need.

What I need is a report (broken down by Employee) or a couple of reports
that can easily tell me the following:

1) I need to know the total Regular and OT hours for each day for each
guide. This is gotten by summing across several different tours and/or
events. So I think I need a report that is grouped by Employee and Date and
then I need to get a sum of total hours for the day and then break that into
Reg and OT hours. In my existing report, I do have a WorkDate footer section
and I tried to put my functions there to get daily totals, but I don't seem
to be getting an accurate sum. The function I used for total hours was
=([TotalHrs]). This seems to be only returning the last value for TotalHrs
rather than a sum. When I try =sum([TotalHrs]), however, I get prompted to
enter TotalHrs. If I enter something like 1 in the prompt box, then I get a
count of the number of tours the guide did, but I'm not getting a sum of
their hours. Any idea what I'm doing wrong here?

2) Assuming I can get a sum of regular and OT hours for the day, then the
next thing I need are weekly totals. On this, I need more detail than I've
looked at so far.

Now I need to break out:

1) Total regular tour hours
2) Total OT tour hours
3) Total regular event hours
4) Total OT event hours

There is an additional level of complexity here in that the regular & OT
hours that I can compute now only tell me about Reg & OT hours in a given
day. But I also need to check to make sure the guide doesn't have more than
40 hours in a week. If they do, anything over 40 hours would also be OT.

In a given day, a guide may work both tours and events. Because we pay
different workman's comp rates depending on which type of program the guide
works, we need to keep these separate. Regardless of whether a guide works
tours or events, they go into OT when they work more than 10 hours in a day
or more than 40 hours in a week (so this info is collapsed across Type).
But, I also need to give payroll a summary of Tour (reg & OT) and Event (reg
& OT) hours as they are recorded and paid separately.

And this is where I'm stuck. I have no idea about how to go about getting
all of these different kinds of information. I'd love to be able to do this
in one report, but realize that might not be possible, so a couple of
different reports would be okay provided I can get all of the info.

At the end of the pay period, I need to be able to give my guides a
print-out (or a couple print-outs if necessary) that shows the detail of the
tours and events they worked (so they can compare what I paid them for
against their own records to make sure we didn't miss anything) and I need to
give them a breakdown of regular and OT hours (with corresponding dollar
amounts) for both tours and events so that they can check their paycheck stub
to make sure we paid everything correctly. I also need to give this level of
detail to our payroll company.

Last pay period, all I was able to get out of Access was a day by day
summary of Regular and OT hours. Then I had to go in BY HAND and create all
of the various tallies I needed. Obviously, this is NOT what I want to do
every pay period, so I need to figure out how to do this more efficiently.

I do not know if this is appropriate to ask here or not (and if not, please
excuse my ignorance), but I can see that this project is my bigger than I
imagined and I think I'm in over my head. Can anyone tell me how I might go
about finding someone who can create this application for me for a reasonable
fee? I imagine this is an easy project for someone who is competent with
Accesss. But it's going to take me forever to figure this out on my own, I
fear. If anyone can provide a lead of who might be able to do this for me,
I would be appreciative! If not, I'll just keep plugging away at this on my
own.

Kim

Okay, that's great. You have the structure, and the GROUP BY query.

For calculating pay rates, I would imagine it would be useful to have hours
and fractions of an hour, e.g. 10.5 hours = ten and a half hours.
Personally, I work in minutes, and convert the results to hours (divide by
60) at the end.

The expressions to type into the Field row of your query will be something
like this:

Minutes: DateDiff("n", [StartTime], [EndTime])

OverTime: IIf(DateDiff("n", [StartTime], [EndTime]) > 600, _
DateDiff("n", [StartTime], [EndTime]) - 600)

Regular: DateDiff("n", [StartTime], [EndTime]) Mod 600

It may be possible to work with your existing expressions by wrapping
CVDate() around your expressions to force them to the date/time data type,
but I think that's less desirable than working in minutes.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. Your suggestions are right on target. I've restructured my
database so that there is one record for each tour. I haven't quite
figured
out the Pay aspects, yet, but I think that will come.

Right now, my biggest challenge is to get a report I can use to calculate
payroll this week!

I am hung up on computing regular and overtime hours each day.

Our company operates on flextime -- so anything over 10 hours in a day is
paid at time and a half.

I've created a report that shows me:

WorkDate, StartTime, & EndTime for each tour worked.

These data are grouped by: Employee then by WorkDate, then by EventType

(Our guides can either work tours or events. We have to keep these
separate
for reporting purposes. So for each tour, we designate whether it is a
tour
or an event).

That is all working fine.

In my report, I am now trying to compute total hours, regular hours, and
OT
hours.

I can get total hours okay. I've tried two approaches to doing that
computation. The first is to use the HoursAndMinutes function found on
this
website which returns a string value when you compute EndTime - StartTime.
The variable I created to do this computation is called Total.

I've also tried using

=Sum([EndTime]-[StartTime])*24

This variable was named TotHours

I like how the answer comes out using this method better because it gives
me
hours and part of hours the way my payroll company wants to see them. So
that is good.

Where I am hung up, however, is in computing OT and regular hours.
Anytime
I try to do a computation using either Total or TotHours I get an error
message.

For instance, I tried:

=[TotHours]-10

I'm trying to get OT computed. Ideally, I want something like:

IIF(TotHours>10,10,TotHours)

But that returns and error. So I figured I'd start with something really
simple like =[TotHours]-10, but this returns an #Error answer for all
employees.

I thought maybe my problem had to do with it not treating TotHours as a
number, so I formated it as a general number. Same error.

Does anyone have any ideas about how to handle?

Kim

 

[Allen Browne]

Kim, that's a rather big question, so I'll just answer the first part, and
let you build it from there.

You are stuck with summing TotalHours. If that is a text box on your report
that has an expression such as:
=[Regular] + [Overtime]
you will not be able to use:
=Sum([TotalHours])
but you should be able to repeat the entire expression, like this:
=Sum([Regular] + [Overtime])

As I said in the previous post, I prefer to work in minutes, since that's
easier to sum. If our TotalHours is formatted as hours and minutes, Access
may not understand the data type correctly, and you can run into problems
once the sum is larger than 24 hours.

From there you can use the Sorting And Grouping dialog (View menu, in report
design view) to group by the employee and the date (2 rows in the dialog)
and sum values into the group footer.

All the best for the rest of this. It is not a trivial task.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Again, Allen. I've changed everything to match what you suggested
and
the time functions are all working well now.

But, of course, I now have a new challenge. Sorry for the newbie
questions,
but I'm not nearly as familiar with Access as I am Excel. However, Access
seems to be the better choice for this project so I'm trying to educate
myself!

Anyways, here's the new challenge...

What I have so far is a query and report that gives me a one page summary
for each employee showing me:

WorkDate, StartTime, EndTime, TotalHours, Type (event or tour), RegHours,
OTHours, and Gratuities.

What this gives is a one line summary for each tour ore event my guides
worked. However, this is not really what I need.

What I need is a report (broken down by Employee) or a couple of reports
that can easily tell me the following:

1) I need to know the total Regular and OT hours for each day for each
guide. This is gotten by summing across several different tours and/or
events. So I think I need a report that is grouped by Employee and Date
and
then I need to get a sum of total hours for the day and then break that
into
Reg and OT hours. In my existing report, I do have a WorkDate footer
section
and I tried to put my functions there to get daily totals, but I don't
seem
to be getting an accurate sum. The function I used for total hours was
=([TotalHrs]). This seems to be only returning the last value for
TotalHrs
rather than a sum. When I try =sum([TotalHrs]), however, I get prompted
to
enter TotalHrs. If I enter something like 1 in the prompt box, then I get
a
count of the number of tours the guide did, but I'm not getting a sum of
their hours. Any idea what I'm doing wrong here?

2) Assuming I can get a sum of regular and OT hours for the day, then the
next thing I need are weekly totals. On this, I need more detail than
I've
looked at so far.

Now I need to break out:

1) Total regular tour hours
2) Total OT tour hours
3) Total regular event hours
4) Total OT event hours

There is an additional level of complexity here in that the regular & OT
hours that I can compute now only tell me about Reg & OT hours in a given
day. But I also need to check to make sure the guide doesn't have more
than
40 hours in a week. If they do, anything over 40 hours would also be OT.

In a given day, a guide may work both tours and events. Because we pay
different workman's comp rates depending on which type of program the
guide
works, we need to keep these separate. Regardless of whether a guide
works
tours or events, they go into OT when they work more than 10 hours in a
day
or more than 40 hours in a week (so this info is collapsed across Type).
But, I also need to give payroll a summary of Tour (reg & OT) and Event
(reg
& OT) hours as they are recorded and paid separately.

And this is where I'm stuck. I have no idea about how to go about getting
all of these different kinds of information. I'd love to be able to do
this
in one report, but realize that might not be possible, so a couple of
different reports would be okay provided I can get all of the info.

At the end of the pay period, I need to be able to give my guides a
print-out (or a couple print-outs if necessary) that shows the detail of
the
tours and events they worked (so they can compare what I paid them for
against their own records to make sure we didn't miss anything) and I need
to
give them a breakdown of regular and OT hours (with corresponding dollar
amounts) for both tours and events so that they can check their paycheck
stub
to make sure we paid everything correctly. I also need to give this level
of
detail to our payroll company.

Last pay period, all I was able to get out of Access was a day by day
summary of Regular and OT hours. Then I had to go in BY HAND and create
all
of the various tallies I needed. Obviously, this is NOT what I want to do
every pay period, so I need to figure out how to do this more efficiently.

I do not know if this is appropriate to ask here or not (and if not,
please
excuse my ignorance), but I can see that this project is my bigger than I
imagined and I think I'm in over my head. Can anyone tell me how I might
go
about finding someone who can create this application for me for a
reasonable
fee? I imagine this is an easy project for someone who is competent with
Accesss. But it's going to take me forever to figure this out on my own,
I
fear. If anyone can provide a lead of who might be able to do this for
me,
I would be appreciative! If not, I'll just keep plugging away at this on
my
own.

Kim

Okay, that's great. You have the structure, and the GROUP BY query.

For calculating pay rates, I would imagine it would be useful to have
hours
and fractions of an hour, e.g. 10.5 hours = ten and a half hours.
Personally, I work in minutes, and convert the results to hours (divide
by
60) at the end.

The expressions to type into the Field row of your query will be
something
like this:

Minutes: DateDiff("n", [StartTime], [EndTime])

OverTime: IIf(DateDiff("n", [StartTime], [EndTime]) > 600, _
DateDiff("n", [StartTime], [EndTime]) - 600)

Regular: DateDiff("n", [StartTime], [EndTime]) Mod 600

It may be possible to work with your existing expressions by wrapping
CVDate() around your expressions to force them to the date/time data
type,
but I think that's less desirable than working in minutes.

Thanks Allen. Your suggestions are right on target. I've restructured
my
database so that there is one record for each tour. I haven't quite
figured
out the Pay aspects, yet, but I think that will come.

Right now, my biggest challenge is to get a report I can use to
calculate
payroll this week!

I am hung up on computing regular and overtime hours each day.

Our company operates on flextime -- so anything over 10 hours in a day
is
paid at time and a half.

I've created a report that shows me:

WorkDate, StartTime, & EndTime for each tour worked.

These data are grouped by: Employee then by WorkDate, then by
EventType

(Our guides can either work tours or events. We have to keep these
separate
for reporting purposes. So for each tour, we designate whether it is a
tour
or an event).

That is all working fine.

In my report, I am now trying to compute total hours, regular hours,
and
OT
hours.

I can get total hours okay. I've tried two approaches to doing that
computation. The first is to use the HoursAndMinutes function found on
this
website which returns a string value when you compute EndTime -
StartTime.
The variable I created to do this computation is called Total.

I've also tried using

=Sum([EndTime]-[StartTime])*24

This variable was named TotHours

I like how the answer comes out using this method better because it
gives
me
hours and part of hours the way my payroll company wants to see them.
So
that is good.

Where I am hung up, however, is in computing OT and regular hours.
Anytime
I try to do a computation using either Total or TotHours I get an error
message.

For instance, I tried:

=[TotHours]-10

I'm trying to get OT computed. Ideally, I want something like:

IIF(TotHours>10,10,TotHours)

But that returns and error. So I figured I'd start with something
really
simple like =[TotHours]-10, but this returns an #Error answer for all
employees.

I thought maybe my problem had to do with it not treating TotHours as a
number, so I formated it as a general number. Same error.

Does anyone have any ideas about how to handle?

Kim