# Need to select record based on date

A

#### 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:

Employee
Step

GovtWageBaseOvertime
Step
Base
Overtime

(> indicates key fields)

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

For example

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

Employee = 123456
EffectiveDate = 2/15/2010
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!

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)

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 *
(SELECT Min(Temp.EffectiveDate)
WHERE Temp.EffectiveDate >= #12/19/09#

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

(SELECT Employee, Min(EffectiveDate) as CurrentEffective
WHERE EffectiveDate >= #12/19/09#
GROUP BY Employee) as EDate

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

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

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
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!

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

Ann Scharpf said:
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

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
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

KARL DEWEY said:
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)

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