Need to select record based on date

[Ann Scharpf]

The government agency where I work is changing the way it tracks labor cost.
We used to use a flat hourly rate for every employee. Now we are going to
use each employee's actual wage rate. Each person MAY receive a "step
increase" on their anniversary date which is, of course, variable.

I have set up two tables:

GovtEmployeeGradeStep

Employee
EffectiveDate Grade
Step

GovtWageBaseOvertime
Grade
Step

Base
Overtime

(> indicates key fields)

Obviously, I link the two tables by Grade & Step.

I'm having trouble figuring out how to get my query to select the right
employee record to find the appropriate Base rate. EVERY employee will have
a record with an EffectiveDate=10/1/2009 (the first day of the fiscal year).
SOME employees may have an additional record. I was thinking of something
like the Pay Period Ending date >= EffectiveDate then use that rate. The
problem is the PPE date could well be > BOTH records that the employee has in
GovtEmployeeGradeStep.

For example

Employee = 123456
EffectiveDate = 10/1/2009
Grade = 12
Step = 4

Employee = 123456
EffectiveDate = 2/15/2010
Grade = 12
Step = 5

How do I get the cost calculation for PPE 12/19/09 to use Grade 12, Step 4
.... But the cost calculation for PPE 3/27/10 to use Grade 12, Step 5?

I hope my question makes sense. And I'm using Access 2003 if that makes a
difference. As always, thanks for your help!

 

[KARL DEWEY]

You need it two ways. What was the pay when and pay at this time.

I think this will do the WHEN --
WHERE [EffectiveDate] = (SELECT TOP 1 [EffectiveDate] FROM [YourTable] AS
[X_Then] WHERE [X_Then].[EffectiveDate] < [YourTable].[Pay Period Ending
date] ORDER BY [X_Then].[EffectiveDate] DESC)

THIS TIME --
WHERE [EffectiveDate] = (SELECT TOP 1 [EffectiveDate] FROM [YourTable] AS
[X_Then] ORDER BY [X_Then].[EffectiveDate] DESC)

 

[John Spencer]

One method would be to use a correlated subquery to identify the proper
record. Correlated sub-queries can be slow.

I don't know which table would have the the pay period ending date in it o
I've just inserted a hard code date.

SELECT *
FROM GovtEmployeeGradeStep INNER JOIN GovtWageBaseOverTime
ON GovtEmployeeGradeStep.Grade = GovtWageBaseOverTime.Grade
AND GovtEmployeeGradeStep.Step = GovtWageBaseOverTime.Step
WHERE GovtEmployeeGradeStep.EffectiveDate =
(SELECT Min(Temp.EffectiveDate)
FROM GovtEmployeeGradeStep as Temp
WHERE Temp.EffectiveDate >= #12/19/09#
AND Temp.Employee = GovtEmployeeGradeStep.Employee)

Another way to do this would be to use a query that looks like the following.

SELECT GovtEmployeeGradeStep.*
FROM GovtEmployeeGradeStep INNER JOIN
(SELECT Employee, Min(EffectiveDate) as CurrentEffective
FROM GovtEmployeeGradeStep
WHERE EffectiveDate >= #12/19/09#
GROUP BY Employee) as EDate
ON GovtEmployeeGradeStep.Employee = EDate.Employee
AND GovtEmployeeGradeStep.EffectiveDate = EDate.CurrentEffective

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ann Scharpf]

Hi, Karl:

Thanks for responding! When I try to use this method, I get an error
message that says "At most one record can be returned by this subquery." And
then there are actually no results.

I wasn't sure what to enter for the [X_Then], so I left it intact, figuring
that at least it was consistent. That's probably my problem. Here's my SQL.
Can you advise what I should put in place of X_Then? (I don't usually write
the SQL directly. I use the grid/matrix in the Access query screen.)

SELECT eBizHoursRecorded.Employee, eBizHoursRecorded.HourType,
eBizHoursRecorded.PPEdate, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.TotalHours,
GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime
FROM (eBizHoursRecorded INNER JOIN GovtEmployeeGradeStep ON
eBizHoursRecorded.Employee = GovtEmployeeGradeStep.Employee) INNER JOIN
GovtWageBaseOvertime ON (GovtEmployeeGradeStep.Step =
GovtWageBaseOvertime.Step) AND (GovtEmployeeGradeStep.Grade =
GovtWageBaseOvertime.Grade)
WHERE ((("WHERE [EffectiveDate]")=(SELECT TOP 1 [EffectiveDate] FROM
[GovtEmployeeGradeStep] AS [X_Then] WHERE [X_Then].[EffectiveDate] <
[eBizHoursRecorded].[PPEdate] ORDER BY [X_Then].[EffectiveDate] DESC)))
GROUP BY eBizHoursRecorded.Employee, eBizHoursRecorded.HourType,
eBizHoursRecorded.PPEdate, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.TotalHours,
GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime;

Thanks again!

 

[Ann Scharpf]

Never mind! I FINALLY have someone else here at the office who works on
databases and he got it to work. Thanks again for your help!
--
Ann Scharpf

Hi, Karl:

Thanks for responding! When I try to use this method, I get an error
message that says "At most one record can be returned by this subquery." And
then there are actually no results.

I wasn't sure what to enter for the [X_Then], so I left it intact, figuring
that at least it was consistent. That's probably my problem. Here's my SQL.
Can you advise what I should put in place of X_Then? (I don't usually write
the SQL directly. I use the grid/matrix in the Access query screen.)

SELECT eBizHoursRecorded.Employee, eBizHoursRecorded.HourType,
eBizHoursRecorded.PPEdate, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.TotalHours,
GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime
FROM (eBizHoursRecorded INNER JOIN GovtEmployeeGradeStep ON
eBizHoursRecorded.Employee = GovtEmployeeGradeStep.Employee) INNER JOIN
GovtWageBaseOvertime ON (GovtEmployeeGradeStep.Step =
GovtWageBaseOvertime.Step) AND (GovtEmployeeGradeStep.Grade =
GovtWageBaseOvertime.Grade)
WHERE ((("WHERE [EffectiveDate]")=(SELECT TOP 1 [EffectiveDate] FROM
[GovtEmployeeGradeStep] AS [X_Then] WHERE [X_Then].[EffectiveDate] <
[eBizHoursRecorded].[PPEdate] ORDER BY [X_Then].[EffectiveDate] DESC)))
GROUP BY eBizHoursRecorded.Employee, eBizHoursRecorded.HourType,
eBizHoursRecorded.PPEdate, eBizHoursRecorded.Project,
eBizHoursRecorded.Vendor, eBizHoursRecorded.TotalHours,
GovtWageBaseOvertime.Base, GovtWageBaseOvertime.Overtime;

Thanks again!
--
Ann Scharpf

You need it two ways. What was the pay when and pay at this time.

I think this will do the WHEN --
WHERE [EffectiveDate] = (SELECT TOP 1 [EffectiveDate] FROM [YourTable] AS
[X_Then] WHERE [X_Then].[EffectiveDate] < [YourTable].[Pay Period Ending
date] ORDER BY [X_Then].[EffectiveDate] DESC)

THIS TIME --
WHERE [EffectiveDate] = (SELECT TOP 1 [EffectiveDate] FROM [YourTable] AS
[X_Then] ORDER BY [X_Then].[EffectiveDate] DESC)

 

[Ann Scharpf]

Thanks for taking the time to reply, John. We ended up using Karl Dewey's
suggested solution.