[AccessD] Select Within Query

This looks promising....but I got the following error:

You have written a subquery that can return more than one field without
using the EXISTS reserved word in the main query's FROM clause. Revise
the SELECT statement of the subquery to request only one field. (Error
3306)

??

Thanks,

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


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.TEJPAL
Sent: Tuesday, December 12, 2006 12:39 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com