[AccessD] Select Within Query

Keith,

    The query was duly tested at this end. It worked OK. Could you please verify whether there is any duplication of dates (RDate) in your table tblPayRateHistory ? 

    You can make RDate as the primary key field. It will avoid errors and also speed up the query.

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

  ----- Original Message ----- 
  From: Keith Williamson 
  To: Access Developers discussion and problem solving 
  Sent: Tuesday, December 12, 2006 23:59
  Subject: Re: [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