[AccessD] Select Within Query

Keith,

    Sample query given below, should get you the Amount for work hours (WHrs) pertaining to week end pay date (PayDate), based upon the latest hourly Rate from tblPayRateHistory as applicable on PayDate in question. 

    RDate & Rate are the fields in tblPayRateHistory, while EmpName, PayDate & WHrs are the fields in table tblTimesheetRecords.

Best wishes,
A.D.Tejpal
---------------

===============================
SELECT tblTimesheetRecords.*, [WHrs]*(Select Rate from tblPayRateHistory as T1 where T1.RDate=(Select Max(RDate) From tblPayRateHistory AS T2 where T2.RDate<= tblTimesheetRecords.PayDate)) AS Amount
FROM tblTimesheetRecords
ORDER BY tblTimesheetRecords.EmpName, tblTimesheetRecords.PayDate;
===============================

  ----- Original Message ----- 
  From: Keith Williamson 
  To: Access Developers discussion and problem solving 
  Sent: Tuesday, December 12, 2006 01:40
  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