Work Order and Time Database Project Access

[apartain]

I am working in Access for a (what I thought would be) simple Work Order and Time database. I am just starting a VB class in hopes to become a real developer, not just a pretend one.

In this database, an employee inputs the date, work order number, start time and end time.

I need some code to have it determine if the total for that entry is Regular Time or overtime. I'm using: Week: Format([Time Transaction Table].Date,"ww") to group the entries into the correct work week.

Here's the catch: They also have Emergency Call Out Time which is billed at 2.5 time. It doesn't matter how many hours they have worked in the week up to that point, it is still billed as ECO. BUT, the ECO counts toward the 40 hours required before overtime is paid.

Example: It is possible for an employee to have 25 hours of Regular Time (1xpay), 15 hours of ECO time (2.5 pay) and then start receiving overtime (1.5 pay).

Please help!

ang

[mddrains]

Questions:

The employee works 30 hours regular time, then 10 hours call out, then 5 additional hours. Is that last five hours overtime?

The employee works 35 hours regular time, then 10 hours call out. Are five of the 35 hours now overtime?

If the above are both true, store the data in the time record as it's entered and don't worry about the rate. At the end of the period, sum the hours by week for each employee counting the call out time first, then the regular time, and once you get to over 40 hours, start counting the overtime.

[MMcCarthy]

Ok I think the best way to handle this is with more fields to store the various calculations.

You've already sorted out the week number. Although, I would have used the DatePart function as it's more flexible

Syntax:
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])

Example:
DatePart ("ww",[Time Transaction Table].[Date], vbSunday, #01/01/2006#)

Now the fields I think you will need along with their calculations. You may already have some of them.

BTW, I am using square brackets for two different reasons
1) Any names with spaces will need them
2) Date and Week are VBA names so have to be enclosed in square brackets to determine that they are field names and not function calls. I would actually change the names if it's not too much trouble.


[Time Transaction Table]

[Date] which you already have
[Week] see above
[TotalTime] total number of hours worked
[CalloutTime] total number of Callout time
[RegularTime] =IIf([TotalTime] > 40, 40 - nz([CalloutTime],0), [TotalTime] - nz([CalloutTime],0)

This means if total hours are more than 40 then regular hours are 40 minus any callout time. No value in callout time will default to 0. If Total hours are less than 40 then regular hours will be total hours minus callout hours.

[OverTime] =IIf([TotalTime] > 40, [TotalTime] - 40, 0)

This means if total time is over 40 hours then overtime is total hours minus 40, otherwise its 0.

If you need to actually calculate the pay based on an hourly rate:
[PayRate] hourly rate of pay
[TotalPay] =([RegularTime]*[PayRate]) + (([CalloutTime]*2.5)*[PayRate]) + (([OverTime]*1.5)*[PayRate])

If you just need to calculate the total payable hours:

[TotalPayHours] = [RegularTime] + ([CalloutTime]*2.5) + ([OverTime]*1.5)

Any problems, please let me know.

[apartain]

Is this where I'm supposed to reply to you? ...hope so.

I can easily calculate the totals on a form or a report by the total hours-call out hours, etc.

BUT, since every little bit of work has to be allocated to the correct work order, it is vital that I know if that particular entry was regulartime or overtime.

For example: WO 1234 gets regular time through Thursday, and on Friday the employee is on overtime and works for WO 4567. WO 4567 gets charged all overtime. Not fair to 4567, I know, but that's the way it is.

Yes to your first question: If 35 hours are worked, 5 ECO (Emergency Call Out) hours are worked, anything over that is overtime.

No to your second question. If 35 hours are worked, 10 ECO hrs, anything over THAT is overtime. So, I guess what I'm saying is that it is possible to work less than 40 hours of regular time and still receive overtime due to the ECO hours.

EC0 is always billed at 2.5 rate, so it doesn't count as regular or overtime.

Questions:

The employee works 30 hours regular time, then 10 hours call out, then 5 additional hours. Is that last five hours overtime?

The employee works 35 hours regular time, then 10 hours call out. Are five of the 35 hours now overtime?

If the above are both true, store the data in the time record as it's entered and don't worry about the rate. At the end of the period, sum the hours by week for each employee counting the call out time first, then the regular time, and once you get to over 40 hours, start counting the overtime.

[MMcCarthy]

Have you checked out the information in my post. I think that answers all your questions.

[apartain]

I am assuming I do the DatePart in the query. I am getting a compile error and it is putting the vbSunday in square brackets.

Ok I think the best way to handle this is with more fields to store the various calculations.

You've already sorted out the week number. Although, I would have used the DatePart function as it's more flexible

Syntax:
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])

Example:
DatePart ("ww",[Time Transaction Table].[Date], vbSunday, #01/01/2006#)

Now the fields I think you will need along with their calculations. You may already have some of them.

BTW, I am using square brackets for two different reasons
1) Any names with spaces will need them
2) Date and Week are VBA names so have to be enclosed in square brackets to determine that they are field names and not function calls. I would actually change the names if it's not too much trouble.


[Time Transaction Table]

[Date] which you already have
[Week] see above
[TotalTime] total number of hours worked
[CalloutTime] total number of Callout time
[RegularTime] =IIf([TotalTime] > 40, 40 - nz([CalloutTime],0), [TotalTime] - nz([CalloutTime],0)

This means if total hours are more than 40 then regular hours are 40 minus any callout time. No value in callout time will default to 0. If Total hours are less than 40 then regular hours will be total hours minus callout hours.

[OverTime] =IIf([TotalTime] > 40, [TotalTime] - 40, 0)

This means if total time is over 40 hours then overtime is total hours minus 40, otherwise its 0.

If you need to actually calculate the pay based on an hourly rate:
[PayRate] hourly rate of pay
[TotalPay] =([RegularTime]*[PayRate]) + (([CalloutTime]*2.5)*[PayRate]) + (([OverTime]*1.5)*[PayRate])

If you just need to calculate the total payable hours:

[TotalPayHours] = [RegularTime] + ([CalloutTime]*2.5) + ([OverTime]*1.5)

Any problems, please let me know.

[MMcCarthy]

I am assuming I do the DatePart in the query. I am getting a compile error and it is putting the vbSunday in square brackets.

It probably needs to be in quotes.

Anyway just change it to 1 instead it means the same thing.

[apartain]

Sorry to be so confused...
The TOTAL TIME in my query is the end time minus the start time, so is just the total time for that transaction. It is not the total time for the entire week. So, how do I find (within the query) the total time for the week so the record knows where it stands in the big picture?

Example:

Employee: 1000
Scope of Work: Automation (Taxable)
Work Order: 1234
Start: 7am
End: 10 am
Total Hours: 3
RT: 3
OT: 0
ECO: 0

Next record:
Employee: 1000
Scope of Work: Travel (nontaxable)
Work Order 1234
Start: 10am
End: 1pm
Total Hours: 3
RT: 3
OT: 0
ECO: 0

Next record:
Employee: 1000
Scope of Work: Administration (nontaxable)
Work Order 1234
Start: 1pm
End: 5pm
Total Hours: 4
RT: 4
OT: 0
ECO: 0

So, from those 3 entries, total time = 10 hours, all regular time, and WO 1234 gets allocated 100% of those 10 hours.

Currently, I have a sum query totalling the entries as they come in, an update query running via macro once the record is saved to place the hours in the appropriate field (RT, OT, or ECOT), and I'm setting the fields' enabled property to no after the record is saved with a message letting them know changes can't be made by the employee once they save, all via macro.

It is working fine, but the downside is if a change is made later to the record, the whole RT, OT, ECO balance is thrown out of whack. Since the entries are made via update query and not a formula, the supervisors can go in and manually make the changes, but they also have to change anything it might affect for the rest of the week. They're not too happy about that.

Is there any kind of VBA loop or something that can, each time the record is added or modified, go through and add up the total hours for the week up to that point and automatically return the correct type of hours? It would have to update all records for the week, as well.

Ok I think the best way to handle this is with more fields to store the various calculations.

You've already sorted out the week number. Although, I would have used the DatePart function as it's more flexible

Syntax:
DatePart ( interval, date, [firstdayofweek], [firstweekofyear])

Example:
DatePart ("ww",[Time Transaction Table].[Date], vbSunday, #01/01/2006#)

Now the fields I think you will need along with their calculations. You may already have some of them.

BTW, I am using square brackets for two different reasons
1) Any names with spaces will need them
2) Date and Week are VBA names so have to be enclosed in square brackets to determine that they are field names and not function calls. I would actually change the names if it's not too much trouble.


[Time Transaction Table]

[Date] which you already have
[Week] see above
[TotalTime] total number of hours worked
[CalloutTime] total number of Callout time
[RegularTime] =IIf([TotalTime] > 40, 40 - nz([CalloutTime],0), [TotalTime] - nz([CalloutTime],0)

This means if total hours are more than 40 then regular hours are 40 minus any callout time. No value in callout time will default to 0. If Total hours are less than 40 then regular hours will be total hours minus callout hours.

[OverTime] =IIf([TotalTime] > 40, [TotalTime] - 40, 0)

This means if total time is over 40 hours then overtime is total hours minus 40, otherwise its 0.

If you need to actually calculate the pay based on an hourly rate:
[PayRate] hourly rate of pay
[TotalPay] =([RegularTime]*[PayRate]) + (([CalloutTime]*2.5)*[PayRate]) + (([OverTime]*1.5)*[PayRate])

If you just need to calculate the total payable hours:

[TotalPayHours] = [RegularTime] + ([CalloutTime]*2.5) + ([OverTime]*1.5)

Any problems, please let me know.

[MMcCarthy]

SELECT Employee, Sum([Total Hours] As HoursWorked, Sum([RT]) As RegTime,
Sum([OT]) As OverTime, Sum([ECO]) As CalloutTime,
(Sum([RT]) + (Sum([ECO]) * 2.5) + (Sum([OT]) * 1.5) As TotalPayHours
FROM TableQueryName
GROUP BY Employee;

This should help you return the results.

As far as the update query is concerned, I would need to know.

1. What is the full sql of the current query and where are you getting your data from.

2. What tables are being used and any other queries e.g. is the date field in the same table to get the week from.

You have to give me details of all tables and queries concerned or I can't help.