[AccessD] Select Within Query

Keith:

I would write one query which returns the required pay rate for the date in
question and join that to the select query which calculates the payroll.

But this looks like one of those problems which I would solve faster with a
bit of code and a temp table rather than try to make the queries to do it.  

If you want to send me the tables with some test data, we can work it
off-line.

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith Williamson
Sent: Monday, December 11, 2006 12:10 PM
To: Access Developers discussion and problem solving
Subject: [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/> 

 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.5.432 / Virus Database: 268.15.15/581 - Release Date: 12/9/2006
3:41 PM