Need to determine overtime in query 1

[dbar10]

I have been working on this now for weeks and I am stuck. I hope that someone can help me with a solution. The following queries are working together to calculate hours worked and rate of pay for a worker at a particular client. Each worker may have different rates of pay at each individual client. All this works great up to the point that I need to figure overtime for each worker. I need to include a running sum of hours worked so I can determine the hours and rate of pay that is entitled to overtime. (Over 40 hours in a week.) You will notice that I have labeled each record with a week number because sometimes workers turn in time cards late and the overtime week calculation needs to take that into consideration as well. This is too complicated to get my head around now. I can’t find any way to get a running sum in a query. I tried outputting it to a report but the report that I need groups the hours worked by worker then each client. This separates the dates so that I can’t determine when the overtime starts. Ideally I need to determine this in the query if possible.
Final query is listed next with supporting queries and tables below it. Please help!!!!


SchedulePayrollQry:
SELECT Schedule1.SchedID, Schedule1.WorkerID, [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle AS WorkerName, [Worker table].SSN, WorkStatus.WorkStatus, Jobs.Job, Category.Category, Schedule1.ClientID AS ClientIdent, [Client Table].LastName & ", " & [Client Table].FirstName & " " & [Client Table].Middle AS ClientName, SchedDay.Day, Schedule1.Date, IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom+7) And [Date]<=(Forms!WorkerPayRates!PeriodTo+7),0,IIf([Date]>=Forms!WorkerPayRates!PeriodFrom And [Date]<=Forms!WorkerPayRates!PeriodTo,1,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-7) And [Date]<=(Forms!WorkerPayRates!PeriodTo-7),2,IIf([Date]>=(Forms!WorkerPayRates!PeriodFrom-14) And [Date]<=(Forms!WorkerPayRates!PeriodTo-14),3,4)))) AS Week, Schedule1.Start, Schedule1.End, IIf([Cancel]=0,(DateDiff("n",[Start],[End]+IIf([End]<[Start],1,0))/60),0) AS [Hours worked], IIf([Salary]>0 And [Hours worked]>=4,8,IIf([Salary]>0 And [Hours worked]<4,[Hours worked],IIf([Salary]=0,[Hours worked],[Hours worked]))) AS [Hours Paid], Schedule1.Cancel, Schedule1.Verified, Schedule1.VerifyDate, WorkClientPayrollQry.Hourly, WorkerPayrollQry.Hourly, IIf(IsNull(WorkClientPayrollQry.Hourly),WorkerPayrollQry.Hourly,WorkClientPayrollQry.Hourly) AS PayRate, Schedule1.Holiday, IIf([Holiday]=On,([Hours Paid]*[PayRate])*1.5,[Hours Paid]*[PayRate]) AS ShiftPay, IIf([Salary]>0,"Salary","Hourly") AS PayType, Schedule1.Mileage AS Miles, IIf(IsNull(WorkClientPayrollQry.Mileage),WorkerPayrollQry.Mileage,WorkClientPayrollQry.Mileage) AS MilesRate, [Miles]*[MilesRate] AS MileagePaid, Schedule1.Misc, Schedule1.MiscQty, Schedule1.PayMiscRate, [MiscQty]*[PayMiscRate] AS MiscPaid, WorkerPayrollQry.WorkEligOvertime, Schedule1.[Pay / Bill], WorkerPayrollQry.WorkEligHoliday
FROM (Category INNER JOIN (WorkStatus INNER JOIN [Worker table] ON WorkStatus.WorkStatusID = [Worker table].WorkStatusID) ON Category.CategoryID = [Worker table].CategoryID) INNER JOIN (SchedDay INNER JOIN ([Client Table] INNER JOIN (WorkerPayrollQry INNER JOIN (WorkClientPayrollQry RIGHT JOIN (Schedule1 INNER JOIN Jobs ON Schedule1.JobID = Jobs.JobID) ON (WorkClientPayrollQry.ClientID = Schedule1.ClientID) AND (WorkClientPayrollQry.WorkerID = Schedule1.WorkerID)) ON WorkerPayrollQry.WorkerID = Schedule1.WorkerID) ON [Client Table].ClientID = Schedule1.ClientID) ON SchedDay.DayID = Schedule1.Day) ON [Worker table].WorkerID = Schedule1.WorkerID
WHERE (((Schedule1.Date)<=[Forms]![WorkerPayRates]![PeriodTo]) AND ((Schedule1.Verified)=-1) AND ((Schedule1.VerifyDate)>=[Forms]![WorkerPayRates]![VerifiedFrom] And (Schedule1.VerifyDate)<=[Forms]![WorkerPayRates]![VerifiedTo]) AND ((Schedule1.[Pay / Bill])=1 Or (Schedule1.[Pay / Bill])=2))
ORDER BY [Worker table].LastName & ", " & [Worker table].FirstName & " " & [Worker table].Middle, Schedule1.Date, Schedule1.Start;
WorkerPayrollQry
SELECT WorkerWagetbl.DefaultWageID, WorkerWagetbl.WorkerID, WorkerWagetbl.AssignDateHr, WorkerWagetbl.ExpireDateHr, WorkerWagetbl.Hourly, WorkerWagetbl.Mileage, WorkerWagetbl.Salary, WorkerWagetbl.Period, [Worker table].WorkEligHoliday, [Worker table].WorkEligOvertime
FROM WorkerWagetbl INNER JOIN [Worker table] ON WorkerWagetbl.WorkerID = [Worker table].WorkerID
WHERE (((WorkerWagetbl.AssignDateHr)<=[Forms]![WorkerPayRates]![VerifiedFrom]) AND ((WorkerWagetbl.ExpireDateHr) Is Null Or (WorkerWagetbl.ExpireDateHr)>[Forms]![WorkerPayRates]![VerifiedFrom]));
Tables for WorkerPayrollQry

WorkerWagetbl:

DefaultWageID AutoNumber
WorkerID Number
AssignDateHr Date/Time Short date
ExpireDateHr Date/Time ShortDate
Hourly Currency
Mileage Currency
Salary Currency
Period Number Lookup: Hour/Week/Month

Joined to: Worker table

WorkerID AutoNumber
WorkStatusID Number - Lookup
LastName Text
FirstName Text
Middle Text
etc. other demographic info

WorkClientPayrollQry:

SELECT WorkClientWagetbl.WorkerID, WorkClientWagetbl.ClientID, WorkClientWagetbl.AssignDateHr, WorkClientWagetbl.ExpireDateHr, WorkClientWagetbl.Hourly, WorkClientWagetbl.Mileage, WorkClientWagetbl.Period
FROM WorkClientWagetbl
WHERE (((WorkClientWagetbl.AssignDateHr) Is Null Or (WorkClientWagetbl.AssignDateHr)<=[Forms]![WorkerPayRates]![VerifiedFrom]) AND ((WorkClientWagetbl.ExpireDateHr) Is Null Or (WorkClientWagetbl.ExpireDateHr)>=[Forms]![WorkerPayRates]![VerifiedFrom]));

WorkClientWagetbl:

ClientWageID AutoNumber
WorkerID Number
ClientID Number
AssignDateHr Date/Time Short date
ExpireDateHr Date/Time Short Date
Hourly Currency
Mileage Currency
Period Number Lookup – Hourly or Salary

Schedule1: (table)

SchedID AutoNumber
MasterSchedID Number
DaySchedID Number
ClientID Number
WorkerID Number
AuthID Number
JobID Number
ProgramID Number
Date Date/Time Short Date
Day Number
Start Date/Time Short Date
End Date/Time Short Date
Verified yes/No
VerifiedDate Date/Time ShortDate
Cancel Yes/No
Reason Number Lookup
NoNeed Yes/No
CancelMaster Yes/No
CancelMasterDate Date/Time Short Date
Holiday Yes/No
Mileage Number
Misc Number Lookup
MiscQty Number
PayMiscRate Currency
Pay/Bill Number Lookup

 

[ZiggyS1]

I don't see why you need a running sum, what you need to do is use "Group by" and sum the hours to the week for each worker, then you can calculate the OT.

 

[kjv1611]

Also if you clean up your code's appearance, it'd be easier to look through and troubleshoot. One way to help would be in using Aliases for the table names, and then add in some spacing/tabbing where necessary.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

The running sum is necessary because the hourly rates for each worker can be different depending on the client they are serving. Knowing what rate to base overtime on means that I have to be able to determine exactly where the overtime kicked in. Not just on the number of hours in the week.

 

[dbar10]

I'm sorry I don't know what you mean by alias and tabbing and spacing where necessary

 

[kjv1611]

SQL Alias:

http://beginner-sql-tutorial.com/sql-aliases.htm

Tabbing and Spacing:

Something like:

SELECT	field1 ,field2 ,field3
FROM	MyTable
WHERE	(field1 > 1) AND
	(field2 IS NULL)

Or put it together:
[tt]

SELECT	a.field1 ,a.field2 ,a.field3 ,b.field1 AS [B_Field1]
FROM	ATable a
		INNER JOIN
	BTable b
		ON a.IDfield = b.IDfield
WHERE	(a.field1 > 1) AND
	(a.field2 IS NULL) AND
	(a.field3 NOT IN(
				SELECT	TOP 100 b1.field3
				FROM	BTable b1
			) x
	)

SELECT	a.*
FROM	ATable a
WHERE	a.field2 = "monkey"

[/tt]

Of course, I just made-up all the examples. If I can get around to it, I'll take what you put, and break it down that way, to give you possibly a better idea of what I'm talking about.

Also, looking at some different standards lists/guidelines available on the web wouldn't hurt. Here's one in PDF format (you click a link within the site for the PDF):

http://www.pinaldave.com/best-sql-s...andards-and-Guidelines-Complete-List-Download

That was referenced from here:

http://blog.sqlauthority.com/2007/0...ndards-and-guidelines-complete-list-download/

But his link from this page goes to a PDF file that just leads to the current one, the link I listed here.

That particular site seems to have a lot of good SQL info, I suppose that makes sense, being that the author is some sort of MS SQL MVP.

Well, my examples don't always come across lined up the same on the forum posting as they do in notepad or in MS SQL Management Studio either. Oh well.

Hopefully that'll give some food for thought, at least.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

I can generate a report that will do what I want. Can I then take results from that report and use it as criteria for another report?

 

[kjv1611]

Well, your report has to be based on a query, I would think. You can use that same query to generate the other report, just changing what data you need. You could simply copy that query into another query, and edit there, then build your report off the new query.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

The query won't give me the running sum that I need but I can do the calculations and running sum in the report. If I just group on the workers. But then I need this information broken down on another report that Groups workers and then on clients. This is where I have the big problem. I can't build a query to give me what the first report will do, so I was hoping that there would be a way to capture the information calculated on the first report and use that for the second report. Ever heard of that being done?

 

[kjv1611]

You can do your GROUP BYs in the queries. That's no biggie.

If you open a query in design view, and click on the big E, that'll give you all your Sum/Group options for every column. Just leave anything at Group By which you don't want to just give you a Sum, Average, or whatever.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Or if you're query is just SQL code within VBA, you'll just need to add/change your GROUPing options.


SELECT a.Field1 ,a.Field2 ,COUNT(a.Field3) AS [CountFields]
FROM MyTable a
GROUP BY a.Field1 ,a.Field2

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

Thanks kjv1611, I am not communicating my problem well enough. Grouping is not the issue. I need a running sum in the query or report. I have to be able to assign the running sum to each worker by week so that I can tell where overtime kicks in for that worker. Now some workers have different rates of pay depending on which client they are serving. That's why the challenge. If a worker's base rate is 9.00 an hour then overtime would be calculated on that. That's easy. Here's the rub. If that worker has another client that she gets paid 11.00 an hour for and she work's overtime that week, I need to be able to see what day and time she worked overtime for which client so that I can determine the overtime rate. So far I can only get this info from a report with running sum grouped only on worker name. But I also need a report that group's by worker and then grouped on clients for that worker with the rate of pay and hours worked on it. I hope this makes more sense now. Do you see my problem? I really appreciate your trying to help.

 

[kjv1611]

Well, I've got an idea or two, but it's going back to the table setup.

What I'm thinking is that you could either:
1. Have one table per employee (if there aren't too many) which could house all the known possibilities, even if they don't seempossible - as far as clients, rates, etc..

But that probably would not be a great use of a database....

The other idea, maybe, is to create one query per Employee... or else have VBA code create the query/SQL on the fly, pulling all necessary pieces together for said employee.... then you have another query or table that grabs all of thsoe together...

Say you've got a back end and front end to the database. Or perhaps you could have multiple back-ends - one per year, perhaps? Then, on the front end, you've got all the calculations. On the back end databases, you could have one table per week or pay period.

So your queries could append the necessary data each week/pay period to the respective tables in the back-end database(s).

This may seem crazy, but it's just a thought. Obviously you'd need to clean it up and form it into a definitive plan to use it!

--

"If to err is human, then I must be some kind of human!" -Me

 

[lespaul]

Have one table per employee

that completely breaks the rules of normalization...I wouldn't do that at all...is there any reason you are only using queries? why not just write a small function that gives you the information you need?

Leslie

 

[kjv1611]

Yeah, that's sorta what I meant by:

But that probably would not be a great use of a database....

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

Wow, I will consider that. Thanks This is a front end - back end database by the way.
I don't see how that is going to solve my problem. Let me give you a scenario:

Worker: Suzie
Schedule
Date Client Rate Shift Day Hrs RunSum
12/1/09 D smith 9.00 10 AM - 2 PM Monday 4 4
12/1/09 C Bon 11.00 3PM - 6 PM Monday 3 7
12/2/09 D Smith 9.00 8 AM - 2 PM Tues 4 11
12/2/09 M Taylor 9.50 2:30PM - 7:00PM Tues 4.5 15.5
12/3/09 C Bon 11.00 7 AM - 2 PM Wed 7 22.5
12/3/09 D Smith 9.00 2:30 PM - 8 PM Wed 5.5 28
12/4/09 C Bon 11.00 8 AM - 3 PM Thur 7 35
12/4/09 M Taylor 9.50 4 pm - 8 pm Thur 4 39
12/5/09 D Smith 9.00 8 AM - 2 pM Fri 6 45
12/5/09 C Bon 11.00 3 pm - 8 pm Fri 5 50

From the example this could be a typical work week. Because of different rates and clients I need to query where the overtime begins and what rate to pay for each hour over 40. If I can get a running sum then I can write conditions to calculate the pay rate for overtime on each shift. This is a bear. Does this help?

 

[dbar10]

lespaul, I wouldn't even know where to begin on writing a function. If you have any ideas I would sure appreciate it.

As you can see from the example above this is pretty complicated. I am currently getting all the information fine that I have described exept the runsum. If I could get that, I am sure I can work out the rest.

 

[kjv1611]

Well, obviously, your running sum idea also won't work in this scenerio, at least not for getting a total pay, and you really don't need it to total all the hours, b/c you can do that in a quick SUM.

But as lespaul said, you can use a function to get the data you need.

So you could have these fields in the table:
[blue]EmployeeID[/blue]
Date
Client
Rate
Shift
Day
Hours
[blue]PayEarned[/blue]

Then use a function to fix that... and if it's in Access, and it's not too big - which if it is "too" big for this, you might better look at something other than Access anyway.. you could do a VBA procedure like this:

Private Sub CalcPay()
  Dim db As DAO.Database
  Dim rs As DAO.Recordset
  
  Set db = CurrentDb
  Set rs = tblCompensation

  Do While Not rs.EOF
    rs.Edit
    rs.Fields("PayEarned") = rs.Fields("Hours") * rs.Fields("Rate")
    rs.Update
    rs.MoveNext
  Loop
  
  rs.Close
  db.Close
  Set rs = Nothing
  Set db = Nothing

Or you could do something similar as a function within your SQL query, I'm guessing that's specifically what lespaul was getting at.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Also, how many employees are we talking about here? How large of a company? I've no idea of the costs, but ADP (and I'm sure others) has seemingly some pretty good systems for all of this. I know that, b/c we use ADP now where I work, and I think it's made things a lot simpler on the HR side, as far as administering it all.

--

"If to err is human, then I must be some kind of human!" -Me

 

[dbar10]

The query I have above is already calculating total pay just fine until I hit overtime hours. With a running sum it would be easy enough to put a calculated field in the query to say that all hours over 40 on this record would be paid at 1.5 the rate. The Hours Paid are calculated on each record The Sum won't work because that still only gives me total for week But there is not just one rate of pay to calculate overtime on.