[AccessD] Select Within Query

Keith, I'm replying back to the list in case this is helping other members.

In your query:

SELECT T1.EmployeeNumber, T1.TimeWkEnd, (SELECT PayRate FROM
tblPayRateHistory AS T2 WHERE EffectiveDate=(SELECT
Max(EffectiveDate) FROM tblPayRateHistory WHERE
EffectiveDate<=T1.TimeWkEnd  AND
EmployeeNumber=T2.EmployeeNumber) AND EmployeeNumber=T1.EmployeeNumber)
AS CurrentWage
FROM tblPayrollRecords AS T1;

You are using the first method that I suggested.  However, you want to use
more fields, so I would recommend the second method.  You can put more
fields in with the first method, but you are going to be adding the multiple
subquery solution in for each one.  Ie, :

SELECT T1.EmployeeNumber, T1.TimeWkEnd, (SELECT PayRate FROM
tblPayRateHistory AS T2 WHERE EffectiveDate=(SELECT
Max(EffectiveDate) FROM tblPayRateHistory WHERE
EffectiveDate<=T1.TimeWkEnd  AND
EmployeeNumber=T2.EmployeeNumber) AND EmployeeNumber=T1.EmployeeNumber)
AS CurrentWage, (SELECT ServiceCode FROM
tblPayRateHistory AS T2 WHERE EffectiveDate=(SELECT
Max(EffectiveDate) FROM tblPayRateHistory WHERE
EffectiveDate<=T1.TimeWkEnd  AND
EmployeeNumber=T2.EmployeeNumber) AND EmployeeNumber=T1.EmployeeNumber)
AS CurrentServiceCode
FROM tblPayrollRecords AS T1;

This would add ServiceCode.  Lot of SQL to add one field, not too mention
twice the work.  I would recommend this:

SELECT T1.EmployeeNumber, TimeWkEnd, PayRate
FROM tblPayrollRecords AS T1 INNERJOIN tblPayRateHistory ON
T1.EmployeeNumber=tblPayRateHistory.EmployeeNUmber
WHERE EffectiveDate=(SELECT Max(EffectiveDate) FROM tblPayRateHistory WHERE
EmployeeNumber=T1.EmployeeNumber AND EffectiveDate<=T1.TimeWkEnd)

The above SQL (hope it works, I am just writing it in email, not setting up
tables to test it, might have spelling goofs) will do the same thing, but
now, you can just add the fields as regular fields in your main query, ie
(I'm just going to cut and paste the above, and add the fields, so if there
is a spelling goof in the first, it'll be in this one too)

SELECT T1.EmployeeNumber, TimeWkEnd, PayRate, JobCostRate, OvtPercent, Dept,
OfficeCode, Org, ServiceCode
FROM tblPayrollRecords AS T1 INNERJOIN tblPayRateHistory ON
T1.EmployeeNumber=tblPayRateHistory.EmployeeNUmber
WHERE EffectiveDate=(SELECT Max(EffectiveDate) FROM tblPayRateHistory WHERE
EmployeeNumber=T1.EmployeeNumber AND EffectiveDate<=T1.TimeWkEnd)


NOTE:  I am not prefixing any table that doesn't require it.  I'm doing that
so it's easier to see where/how the subqueries are working.  But if any of
these fields are shared in the tables, they must be prefixed, and in a final
revision query, prefixing will make it easier to see what comes from where
in an SQL view.  

Drew



-----Original Message-----
From: Keith Williamson [mailto:Kwilliamson at RTKL.com] 
Sent: Tuesday, December 12, 2006 5:41 PM
To: Drew Wutka
Subject: RE: [AccessD] Select Within Query

Hey Drew....

You lost me on the second part of your post....(not that I am totally
with you on the first part...BUT...I managed to get that to work..lol)

Here is my query, as restated from your first section:
***********************

SELECT T1.EmployeeNumber, T1.TimeWkEnd, (SELECT PayRate FROM
tblPayRateHistory AS T2 WHERE EffectiveDate=(SELECT
Max(EffectiveDate) FROM tblPayRateHistory WHERE
EffectiveDate<=T1.TimeWkEnd  AND
EmployeeNumber=T2.EmployeeNumber) AND EmployeeNumber=T1.EmployeeNumber)
AS CurrentWage
FROM tblPayrollRecords AS T1;

*************************


In addition to Payrate, ideally I'd like to pull in [JobCostRate],
[OvtPercent], [Dept], [OfficeCode], [Org], [ServiceCode].  These are all
in the tblPayRateHIstory table (your example tblEmployeeData).  In the
above, I didn't need the [EndTime] or [StartTime]....I simply use the
hours for the week....which is in the tblPayrollRecords....and do the
calculations for payroll within the query.

What am I missing?  I can't figure out how your second method fits into
the first query....to bring back additional fields.

Thanks again!!

By the way...I am leaving for home now....so I'll be picking up OT in
about an hour.  Hope I can help out there.  :)


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