[AccessD] Select Within Query

LOL..... ok, no groveling necessary (that is only required on OT, and you
and I are usually never on different sides there... <grin>).

Which method worked?  I would guess that the last one, using an inner join
for the EmployeeID field and then using the subquery in the WHERE clause was
the best, and probably most efficient. (I would assume that JET would be
more efficient with a JOIN in the FROM clause, then in the WHERE clause, but
again, it is an assumption, I'm not really sure).

Just remember that the real trick to a subquery is capturing the right
criteria from the main query, or higher level subqueries.  This is done
through the 'aliasing' of the tables.  That's important.  For example:

SELECT Somefield
FROM SomeTable
WHERE Anotherfield=(SELECT ADifferentField FROM SomeOtherTable WHERE
ThisField=SomeTable.SomeField)

In the last part, the subquery has no idea where SomeTable.SomeField is
coming from.  If we put SomeTable I the FROM statement in the subquery, then
it will be getting it's own value.  But by using this:

SELECT Somefield
FROM SomeTable AS T1
WHERE Anotherfield=(SELECT ADifferentField FROM SomeOtherTable WHERE
ThisField=T1.SomeField)

We are now telling the subquery to get the value from the main query.  I've
seen examples where everything is aliased, which I personally think is just
confusing.  I only alias things that need it.

Let me know if you need anymore help...not sure why your first attempts to
subquery didn't work, how were you trying to use them?

Drew


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

OMG!!!!  It works....It Works....IT WORKS!!!!!  :-}

I bow to your Kingness!!!  Not only did it work....but it was fast as
lightening too!!!!

Now...I just have to read through it closer to really understand it.

Thank you sooooo much, Your Highness!!

:))

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