[AccessD] Select Within Query

Keith Williamson Kwilliamson at RTKL.com
Tue Dec 12 13:52:02 CST 2006


Yeah....that is what I am trying to accomplish....and why I've held off
loading the updated payroll...until we finished with the October close.
IF I had loaded the November raises.....my October numbers would have
been wrong.  That's why I am trying to get the effective date (for pay
history) that is the oldest date, still less than the date of the
timecard (ie. Any new history is still older than the timecard.)

I can load the data, into the mda for you.  Actually...I'll just proxy
some new history for a couple employees...just to test the concept.

What is an NDA??

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 Beach Access
Software
Sent: Tuesday, December 12, 2006 2:12 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Select Within Query



Keith:

One possible gotcha with fetching the latest payroll history record - if
a
person's pay rate changes and then you process the payroll for the
period
preceding the increase, returning the most recent payroll history record
will not be right.  You'd have to fetch the second most recent payroll
history record.

Generally, you have to take the pay period and find the record in the
payroll history table that matches that pay period.

Bigger complication but probably not for your app - if the pay rate
changes
in the middle of a pay period then you have to calculate the pay with
the
old rate up to that change date, and at the new rate after the change
date.

I'm sure accounting will say that there's absolutely no chance of that
EVER
happening.


Rocky Smolin
Beach Access Software
858-259-4334
www.e-z-mrp.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Keith
Williamson
Sent: Tuesday, December 12, 2006 10:30 AM
To: Access Developers discussion and problem solving
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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.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.16/582 - Release Date:
12/11/2006
4:32 PM
 

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




More information about the AccessD mailing list