Solved Get Current Employee Rate based on date (1 Viewer)

raziel3

Registered User.
Local time
Today, 14:03
Joined
Oct 5, 2017
Messages
273
Hello all, I've been searching the internet for a solution for a while now. Query and Tables involved

qryPayDetail
EMPID, WorkDay, Basic, Overtime, CurRate

tblERates
EMPID, EffectiveDate, Rate

I want to be able to fill CurRate with current employee rate based on the WorkDay.

For example Records in tblERates:
1. EMPID 1 EffectiveDate 1/1/19 Rate 25 (m/d/yyyy)
2. EMPID 1 EffectiveDate 5/1/19 Rate 30

if WorkDay was 3/31/19 the Rate would be 25
if WorkDay was 5/31/19 the Rate would be 30

I was trying a dlookup but after reading up of similar problems, Dlookups scans the whole table making it inefficient. Also the Dlookup duplicates data in qryPayDetail so I am getting 1 WorkDay and the 2 Rates are being populated.

Can anyone suggest a subquery?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:03
Joined
May 7, 2009
Messages
19,169
select empid, workday, basic, overtime, (select top 1 T1.rate from tblERates as T1
where T1.empid = qryPayDetail.empid where T1.effectivedate <= qrypaydetail.workday
order by T1.empid asc, T1.effectivedate desc) as CurRate
from qryPayDetail;
 

raziel3

Registered User.
Local time
Today, 14:03
Joined
Oct 5, 2017
Messages
273
It works, Thank you. Had to change the second "where" to "and"
 

plog

Banishment Pending
Local time
Today, 13:03
Joined
May 11, 2011
Messages
11,611
Can anyone suggest a subquery?

Since you asked that specifically, yes:

Code:
SELECT tblPayDetails.EMPID, tblPayDetails.WorkDay, Max(tblERates.EffectiveDate) AS MapEffectiveDate
FROM tblERates INNER JOIN tblPayDetails ON (tblPayDetails.WorkDay >= tblERates.EffectiveDate) AND (tblERates.EMPID = tblPayDetails.EMPID)
GROUP BY tblPayDetails.EMPID, tblPayDetails.WorkDay;

Paste that in a new query and name it 'sub1'. It's basically a junction table that sorts out which WorkDay values go to which EffectiveDate records. To use it you would use this query:

Code:
SELECT tblPayDetails.EMPID, tblPayDetails.WorkDay, tblERates.Rate
FROM (tblPayDetails INNER JOIN sub1 ON (tblPayDetails.EMPID = sub1.EMPID) AND (tblPayDetails.WorkDay = sub1.WorkDay)) INNER JOIN tblERates ON (sub1.MapEffectiveDate = tblERates.EffectiveDate) AND (sub1.EMPID = tblERates.EMPID);


Now here's all my notes/caveats:

A DMax will accomplish the same thing and not require these 2 queries. It may be less "effecient", but probably not by much.

My queries use tblPayDetail, not qryPayDetail. If you want true efficiency you should use the data source that qryPayDetail is built upon, not the query itself.

You should have an autonumber primary key in tblERates. That would create a better link for the second query than having to use both the EMPID and MapEffectiveDate fields to JOIN all the tables on.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 02:03
Joined
May 7, 2009
Messages
19,169
sorry, overtyping..
 

raziel3

Registered User.
Local time
Today, 14:03
Joined
Oct 5, 2017
Messages
273
You should have an autonumber primary key in tblERates. That would create a better link for the second query than having to use both the EMPID and MapEffectiveDate fields to JOIN all the tables on.

Duly noted

I may have a problem using the source table in place of qryPayDetail. The table is set up as follows:

EMPID, WorkDay1, TimeIn, WorkDay2, TimeOut

qryPayDetail uses that data to get the total hours worked and how much of it was Overtime. In tblERates, it have the base hours each employee should work so I use qryPayDetail to summarize all that before I start to calculate the pay (Hours Worked x Rate).

I am doing unnecessary work using a query to total the work hours for each employee?
 

plog

Banishment Pending
Local time
Today, 13:03
Joined
May 11, 2011
Messages
11,611
I don't know if you are doing unneccesary work because I don't understand what you are doing.

EMPID, WorkDay1, TimeIn, WorkDay2, TimeOut

Confused. Why does each record have 2 dates in it? Does TimeIn go with WorkDay1 and TimeOut go with WorkDay2? Does TimeIn and TimeOut just store time values--not Date/Time values?

What determines the rate an employee gets? The time of day they are working? The cumulative amount of work they do? Some other factor?
 

raziel3

Registered User.
Local time
Today, 14:03
Joined
Oct 5, 2017
Messages
273
Does TimeIn go with WorkDay1 and TimeOut go with WorkDay2?

Yes I had to do it like that in case of this:
EMPID 1
WorkDay1 8/1/19
TimeIn 10:00pm
WorkDay2 8/2/19
TimeOut 5:00am

I would then concatenate WorkDay1 & TimeIn, WorkDay2 & TimeOut and use the DateDiff function to get the total time for that day.

What determines the rate an employee gets?

Rate is set on tblERates and calculated per hour.

But I get it, you're right I don't need a totals query and then query that again. Everything can be done with one query with a subquery.
 

plog

Banishment Pending
Local time
Today, 13:03
Joined
May 11, 2011
Messages
11,611
I would then concatenate WorkDay1 & TimeIn, WorkDay2 & TimeOut...

Don't do that. It's called a date/time field for a reason--it can hold a date and a time. So get rid of the WorkDay fields and just put the date in with TimeIn and TimeOut. No need to concatenate any more.


Rate is set on tblERates and calculated per hour.

That wasn't really my question. Let me rephrase it--how do you determine what rate class to use? You have more than one rate class for an employee at a time (Basic, Current, Overtime). How do you determine what rate class to use?
 

raziel3

Registered User.
Local time
Today, 14:03
Joined
Oct 5, 2017
Messages
273
Oh, no Basic is the Base amount of hours the employee is supposed to work so on tblERates it is set.

EMPID 1 is set to 8, EMPID 2 is set to 8.5, EMPID 3 (security) is set to 12 etc.

Overtime is calculated at Current Rate x 1.5

Gross will be the Base Hours x Current Rate + Overtime (if any)
 

Users who are viewing this thread

Top Bottom