Advanced Customization

[oihjk]

This should be a piece of cake for someone with a few experience years on me. I have a client for which I am building a custom database. They use this for various reasons. One main reason is to track man hours, cost, billing information on a per job/customer basis.

Each of their customers has a unique contract. Which specifies the information required in order to bill the customer for a job. For example: Customer "Big Factory" requires PO# and Job#.

The contract also specifies the billing rate for each piece of equipment and each type of employee. For example: For customer "Big Factory" again, we charge $10 a day for a pickup truck and $20 an hour for a welder. Customer "Little Factory" has a different contract with different rates and required fields.

*Now I have all of the above under control (in my opinion)*

Following is part is where I struggle. Each contract also specifies which hours (on a daily basis) are standard work hours and which are premium work hours, as well as, at what point (# of weekly hours) to start billing only premium hours during the remaining part of the week. For example: "Big Factory" Standard hours are from 07:00 to 17:00, Premium are from 17:00 to 07:00, Weekly premium start once an employee has worked 40 hours for that week regardless if the employee has worked some hours for another client(*Exceptions do exist where the employee must work 40 hours for the client before the weekly premium kicks in*).

I need this to automatically calculate for each employee. It must update each time sheet that the employee exists on.

Currently I have it working in a rather unorthodox way, but it isn't exactly scalable to work for each customer's unique contract. I can post how I have it, but it will be quite a long post. It has to do with a running sum query then a make table query then an update query.

One other caveat: My client pays their employees premium pay on a 40 hour work week (>40 per week is premium time) regardless of which hours they work. (Seems like they are ripping the employees off I know). I posted this once before, but had received only a negative post chewing me out for not being fair. Anyway...

If presented with this how would you do it?

Any help is greatly appreciated.
Thanks,
Eric

 

[oihjk]

Just to show you, this is part of one of the queries:

strSQL = "SELECT qryListHoursTime.LaborDetailsID, IIf([ContractTypeID]='PT08',IIf([Hours]>8,8,IIf(([RunningSum2]-[Hours])>=8,0,[Hours])),IIf((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime])>40,IIf(IIf(((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime]))>40,40-((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime])-([hours])),0)<0,0,IIf(((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime]))>40,40-((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime])-([hours])),0)),[Hours])) AS STBillHours,"
strSQL = strSQL & "IIf([ContractTypeID]='PT08',IIf([Hours]>8,[Hours]-8,IIf(([RunningSum2]-[Hours])>=8,[Hours],0)), IIf((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime])>40,IIf(((SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime])-(40))>[Hours],[Hours],(SELECT Sum([Hours]) FROM [qryListHoursTime] AS [qryListHoursTime_1] WHERE [qryListHoursTime_1].[DateTime] <= [qryListHoursTime].[DateTime])-(40)),0)) AS OTBillHours, qryListHoursTime.Hours INTO tbTempBillHoursUpdate "
strSQL = strSQL & "FROM ((tbCustomer INNER JOIN (tbContract INNER JOIN tbCustContract ON tbContract.ContractID = tbCustContract.ContractID) ON tbCustomer.CustomerID = tbCustContract.CustomerID) INNER JOIN tbJob ON tbCustomer.CustomerID = tbJob.CustomerID) INNER JOIN (qryFncRunningSumBillingHours RIGHT JOIN (qryListHoursTime INNER JOIN tbDailyWO ON qryListHoursTime.GulfWOID = tbDailyWO.GulfWOID) ON qryFncRunningSumBillingHours.LaborDetailsID = qryListHoursTime.LaborDetailsID) ON tbJob.JobNumber = tbDailyWO.[Job #];"


A nightmare really...

 

[checker]

I'd start by creating a new table that holds all the customers and the criteria for billing, like:
Client|Start Time|End Time|Billing Type
Big Factory|07:00|17:00|Standard
Big Factory|17:00|07:00|Premium

You might even want to add the days of the week.
Once you've got all the clients/billing info setup, it should be easy to determine if rates/hours are standard or premium across the board for everyone.

 

[checker]

Sounds like the hardest part of this is just determining the different type of contracts.

I'd start by creating a new table that holds all the customers and the criteria for billing, like:
Client|Start Time|End Time|Billing Type
Big Factory|07:00|17:00|Standard
Big Factory|17:00|07:00|Premium

You might even want to add the days of the week or other criteria.
Client|Day|Start Time|End Time|Billing Type
Big Factory|MON|07:00|17:00|Standard
Big Factory|TUES|07:00|17:00|Standard
Big Factory|SAT|07:00|17:00|Premium
etc
etc

Once you've got all the clients/billing info setup, it should be easy to determine if rates/hours are standard or premium across the board for everyone.

 

[checker]

Sounds like the hardest part of this is just determining the different type of contracts.

I'd start by creating a new table that holds all the clients/contracts and the criteria for billing that apply, like:
Client or Contract|Start Time|End Time|Billing Type
Big Factory|07:00|17:00|Standard
Big Factory|17:00|07:00|Premium

You might even want to add the days of the week or other criteria.
Client or Contract|Day|Start Time|End Time|Billing Type
Big Factory|MON|07:00|17:00|Standard
Big Factory|TUES|07:00|17:00|Standard
Big Factory|SAT|07:00|17:00|Premium
etc
etc

Once you've got all the clients/billing info setup, it should be easy to determine if rates/hours are standard or premium across the board for everyone.

 

[oihjk]

Hey Checker,

Thanks for the reply. I have been working on this for so long I need some "outside insight." That gets me going in a good direction. On the same note, once I have that set. I will need to be able to determine at which point in the day(also week) an employee reaches overtime. So in the case where a customer's contract specifies that the billing standard hours are between 07:00 to 17:00 and equal or less than 8 hours per day I need to determine at which point the the employee reaches premium time (easy if it's past 17:00 or before 07:00, but what if it is between those but greater than 8?)? How would you go about that? I currently, like I said before am using the "nightmare queries" for this. But a nightmare to customize for each client, and I don't think it will work for some cases.

Thanks for the advice Checker, I will move toward what you said.

Eric

 

[checker]

oihjk

I don;t think I understand the requirements fully. Can you provide some cases like the examples I've put in the attached file ?