[AccessD] Select Within Query

Keith:

Let me know if it works.  No point inventing 2 wheels.

Rocky


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 2:33 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Select Within Query

Wow!!!!  You must be the subquery KING!!!   :))

Haven't tried it yet....but what you wrote LOOKS impressive.  LOL!!

I'll give it a whirl.

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
DWUTKA at marlow.com
Sent: Tuesday, December 12, 2006 4:55 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Select Within Query

LOL.  Ok, holler offlist if you need help (I said Offline in my first
reply,
that would be a bit difficult! <grin>).

Subqueries can be tricky, but the key is how you use them.

For example, I think you are trying to get a field from one table that
doesn't have a match with a field in another table.  Ie, you have date
fields in the timecard table, and date fields in the employeedata table.

So, what you need can be accomplished two ways.  Let's take the
following
data structure:

tblTimeSheet (Fields (EmployeeID, StartTime, EndTime))

tblEmployeeData (Fields (EmployeeID, Wage, TimeStamp))

The goal is to get the proper wage data for the tblTimeSheet data.  The
first way to do this is to use a subquery inside of a subquery.  The
'inner'
subquery finds the correct 'TimeStamp' by getting the Max of TimeStamp
where
TimeStamp is less then or equal to StartTime.  The outer subquery uses
the
inner subquery in the WHERE clause to match TimeStamps, but the SELECT
Clause only returns the Wage field.

Here would be the SQL:

SELECT EmployeeID, StartTime, EndTime, ((EndTime-StartTime)*24) AS
TimeWorked, (SELECT Wage FROM tblEmployeeData AS T2 WHERE
TimeStamp=(SELECT
Max(TimeStamp) FROM tblEmployeeData WHERE TimeStamp<=T1.StartTime  AND
EmployeeID=T2.EmployeeID) AND EmployeeID=T1.EmployeeID) AS CurrentWage
FROM tblTimeSheet AS T1;

Note that the actual query assigns tblTimeSheet as T1, the 'outer'
subquery
assigns tblEmployeeData as T2.  This is so each subquery can match the
appropriate fields.  The outer subquery is only returning the Wage
field, so
you get one value.

This works, however, what if you want to return more then one field from
tblEmployeeData.  In our example, we don't have more fields to match,
but we
can create an inner join with one subquery in the WHERE clause of the
main
query.  That's really all a join is anyways.  When we say tblTable1
INNER
JOIN tblTable2 ON tblTable1.SomeField=tblTable2.SomeField, we are really
saying SELECT .... FROM .... WHERE
tblTable1.SomeField=tblTable2.SomeField.


So, the second method would be this:

SELECT T1.EmployeeID, StartTime, EndTime, ((EndTime-StartTime)*24) AS
TimeWorked, Wage
FROM tblTimeSheet AS T1, tblEmployeeData
WHERE T1.EmployeeID=tblEmployeeData.EmployeeID AND TimeStamp=(SELECT
Max(TimeStamp) FROM tblEmployeeData WHERE EmployeeID=T1.EmployeeID AND
TimeStamp<=T1.StartTime)

But with the above SQL, we actually have a join we are using in the
WHERE
clause, so you could also drop that from the WHERE and put it in the
FROM,
like this:

SELECT T1.EmployeeID, StartTime, EndTime, ((EndTime-StartTime)*24) AS
TimeWorked, Wage
FROM tblTimeSheet AS T1 INNER JOIN tblEmployeeData ON
T1.EmployeeID=tblEmployeeData.EmployeeID
WHERE TimeStamp=(SELECT Max(TimeStamp) FROM tblEmployeeData WHERE
EmployeeID=T1.EmployeeID AND TimeStamp<=T1.StartTime)

Either of these two would allow you to put other fields from
tblEmployeeData
in the SELECT line, and have the applicable data appear.

Hope this helps,

Drew

-----Original Message-----
From: Keith Williamson [mailto:Kwilliamson at rtkl.com] 
Sent: Tuesday, December 12, 2006 2:33 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Select Within Query

Yeah...I was trying to handle it with subqueries....but it kept coming
back that the subquery can't return more than one result....which I
couldn't figure out.  

If we can't make any headway (I've already got two different people
taking stabs at it), I'll certainly enlist your help.  :)  Hate to call
in ALL my chips, at once (assuming I even have any chips....I could be
bluffing about the chips).  :)

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

-- 
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