[AccessD] Select Within Query

Hi guys....I got another question for ya.  :-)

 

I have to tables:

 

tblTimesheetRecords:    which holds employee hours information, by
weekly timecard end date;

tblPayRateHistory:        which hold history of employee pay rate,
department, and title.

 

I want to run a query that calculates the payroll detail for a given Pay
Period (ie. Timecard end date).  When the query gets to the field for
payrate, I'd like to have a select query to go out to the
tblPayRateHistory table, and find the oldest effective date which is
less than, or equal to, the timecard end date...for each employee.
This would then be calculating the proper pay rate for that timecard.

 

I could do this at the form level, and just populate the correct rate
with a sql select.  But I really need this at the query level.  The
thing is that ultimately, there will be many records in
tblPayRateHistory, for each employee.  I need to be able to select the
oldest effective date, that is still less than the timecard end date.
Does that make sense?  Can this be done on the query level?  What makes
it hard is that the effective date does no correlate to the timecard end
date....or I'd just link them.  So it actually has to do a comparison,
for each employee.

 

Part of thinks this should be easy...but I haven't been able to get the
right results....so maybe not.

 

Any help is appreciated.

 

Regards, 

 

Keith E. Williamson | Assist. Controller| kwilliamson at rtkl.com

RTKL Associates Inc. | 901 South Bond Street | Baltimore, Maryland
21231-3305

410-537-6098 direct | 410-276-2136 fax | www.rtkl.com
<http://www.rtkl.com/>