<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT size=2>Friends</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>I involved an acquaintance ( outside of AccessD ) that
has</FONT></DIV>
<DIV><FONT size=2>published a couple of books on SQL and Access. His
answer</FONT></DIV>
<DIV><FONT size=2>basically confirms what We've already concluded
about</FONT></DIV>
<DIV><FONT size=2>it being undocumented; but in it's entirety his
response might</FONT></DIV>
<DIV><FONT size=2>provide some more insight into the question.</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>Dave</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2>=================</FONT></DIV>
<DIV><FONT size=2>
<DIV><SPAN class=465153501-09052003><FONT face=Arial color=#0000ff
size=2>Dave-</FONT></SPAN></DIV>
<DIV><SPAN class=465153501-09052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=465153501-09052003><FONT face=Arial color=#0000ff size=2>I
can't remember exactly how I discovered it for 97. I think I found the [
... ]. syntax saved by 2000 (examine the SQL property after you save the
query). Tried it in 97, and it worked! Apparently JET 3 supported
it, but it was undocumented. They updated the syntax parser in 2000 to
accept the ANSI syntax and then "morph" it to the undocumented syntax that JET
understands.</FONT></SPAN></DIV>
<DIV><SPAN class=465153501-09052003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=465153501-09052003>
<DIV align=left><FONT face=Arial size=2>John Viescas, author</FONT></DIV>
<DIV align=left><FONT face=Arial size=2><I>Microsoft Office Access 2003 Inside
Out</I> (coming soon)</FONT></DIV>
<DIV align=left><EM><FONT face=Arial size=2>Running Microsoft Access
2000</FONT></EM></DIV>
<DIV align=left><EM><FONT face=Arial size=2>SQL Queries for Mere
Mortals</FONT></EM></DIV>
<DIV align=left><FONT face=Arial size=2><A
href="http://www.viescas.com/">http://www.viescas.com/</A></FONT></DIV></SPAN></DIV></FONT></DIV>
<DIV><FONT size=2></FONT> </DIV>
<DIV><FONT size=2></FONT> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=john@winhaven.net href="mailto:john@winhaven.net">John Bartow</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A title=accessd@databaseadvisors.com
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Thursday, May 08, 2003 12:45
PM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] SQL in-line
subquery</DIV>
<DIV><FONT size=2></FONT><BR></DIV>
<DIV>Ah, man on a mission - I found the issue of Access Advisor.<BR><BR>It was
December 2001 Advisor Tips<BR><BR>"Creating Derived Tables in
Jet"<BR><BR>Access 2002/200/97<BR><BR>SQL Server lets you create derived
tables, which are SELECT statements that<BR>appear in the FROM clause of a
query. In other words, you can select from<BR>the resultset returned by a
nested SELECT statement. There is no documented<BR>way to accomplish
this in a Jet query without using a saved nested query,<BR>but there is a, but
there is a little-known synatx that makes it possible.<BR>The following query
runs in Access 97 and uses a derived table named "GIO"<BR>which is not a saved
object. The derived table must be enclosed in square<BR>brackets and
terminated with a period: [<Select Staement>].<BR><BR>SELECT
Products.ProductName, Products.UnitPrice,
Gio.CatAvg,<BR>Categories.CategoryName<BR>FROM (Products INNER JOIN [SELECT
Products.CategoryID,<BR>Categories.CategoryName, Avg(Products.UnitPrice) AS
CatAvg<BR>FROM Categories<BR>INNER JOIN Products<BR>ON Categories.CategoryID =
Products.CategoryID<BR>GROUP BY Products.CategoryID, Categories.CategoryName].
AS Gio ON<BR>Gio.CategoryID = Products.CategoryID) INNER JOIN Categories
ON<BR>Products.CategoryID = Categories.CategoryID<BR>ORDER BY
Products.ProductID;<BR><BR>-Giovanni Caruso, via Andy Barron, Contributing
Editor<BR><BR>Open Northwind DB and paste the listed select statement into the
SQL window<BR>of a query, save it and then run it to see the results. Don't
try to run<BR>before saving though because the query design grid will mess it
up really<BR>bad.<BR><BR>I checked the archives of every other access list I
know of and other than<BR>"Paul's" response to Gustav on AccessD in mid 2001
it is the earliest<BR>reference to this syntax I can find. Given that
Giovanni's tip was probably<BR>submitted months before it was actually
published it would be hard to know<BR>which was first. It could be coincidence
that both of them originated at<BR>roughly the same time. But then again, I
have seen a lot of tips in<BR>newsletters and magazines, from both readers and
staff, that seem to arrive<BR>shortly after an item was discussed on one list
or another! Maybe Giovanni<BR>saw the tip here and really wanted one of those
t-shirts from Access<BR>Advisor!<BR><BR>JB<BR><BR><BR><BR><FONT
size=2> -----Original Message-----<BR> From: <A
href="mailto:accessd-bounces@databaseadvisors.com">accessd-bounces@databaseadvisors.com</A><BR> [mailto:accessd-bounces@databaseadvisors.com]On
Behalf Of Gustav Brock<BR> Sent: Thursday, May 08, 2003 10:15
AM<BR> To: <A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A><BR> Subject:
Re: [AccessD] SQL in-line subquery</FONT></DIV>
<DIV><FONT size=2></FONT> </DIV><FONT size=2>
<DIV><BR> Hi Lambert</DIV>
<DIV> </DIV>
<DIV>It seems to me that the [Select * from Foo]. syntax allows
one to create queries in Access that use a sub-query as the base
table. In other words you don't need a temporary table or an already
saved query. This is something I've often wished I could do, if only
the [ ]. syntax was documented somewhere.</DIV>
<DIV> </DIV>
<DIV>Which begs the question - just how did this special syntax get
discovered? It always intrigues me when someone pops out of the woods
and says "hey look at this undocumented feature..."</DIV>
<DIV> </DIV>
<DIV>It was someone called Paul in July 2001 that mentioned it as
common knowledge ...</DIV>
<DIV> </DIV>
<DIV>/gustav</DIV>
<DIV> </DIV>
<DIV> _______________________________________________<BR> AccessD
mailing list<BR> <A
href="mailto:AccessD@databaseadvisors.com">AccessD@databaseadvisors.com</A><BR> <A
href="http://databaseadvisors.com/mailman/listinfo/accessd">http://databaseadvisors.com/mailman/listinfo/accessd</A><BR> Website:
<A
href="http://www.databaseadvisors.com">http://www.databaseadvisors.com</A></DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV> </DIV>
<DIV></FONT><BR>_______________________________________________<BR>AccessD
mailing list<BR><A
href="mailto:AccessD@databaseadvisors.com">AccessD@databaseadvisors.com</A><BR><A
href="http://databaseadvisors.com/mailman/listinfo/accessd">http://databaseadvisors.com/mailman/listinfo/accessd</A><BR>Website:
<A
href="http://www.databaseadvisors.com">http://www.databaseadvisors.com</A><BR></DIV></BLOCKQUOTE></BODY></HTML>