Keith Williamson
Kwilliamson at RTKL.com
Tue Dec 12 14:17:25 CST 2006
Oh. No. I don't need an NDA....there are names in that table...but no
SSN, or addresses. Just don't publish the list, eh? :)
That is exactly what I am trying to do with the routine. I want to be
able to, at some point in the future, re-run payroll reports....and have
it extract the appropriate fields for that time period. This has been
perplexing me, for some reason. It seems like it should be easy. The
thing is....I'm sure I can do it at the report/form level....but would
like to see if I can do it at the query level. Does that make sense?
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
-----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 3:04 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Select Within Query
Non-disclosure agreement.
If you write the routine so that it picks the right history record based
on
pay period then you don't have to worry about running a payroll for a
previous period. Or a report - if someone wants a payroll report for 6
months ago, it would have to find the pay rate at that time.
And you can update the pay history as it happens instead of waiting to
do
them in a batch.
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 11:52 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Select Within Query
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
--
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