dave sharpe
davesharpe2 at cox.net
Thu May 8 21:13:07 CDT 2003
Friends I involved an acquaintance ( outside of AccessD ) that has published a couple of books on SQL and Access. His answer basically confirms what We've already concluded about it being undocumented; but in it's entirety his response might provide some more insight into the question. Dave ================= Dave- 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. John Viescas, author Microsoft Office Access 2003 Inside Out (coming soon) Running Microsoft Access 2000 SQL Queries for Mere Mortals http://www.viescas.com/ ----- Original Message ----- From: John Bartow To: accessd at databaseadvisors.com Sent: Thursday, May 08, 2003 12:45 PM Subject: RE: [AccessD] SQL in-line subquery Ah, man on a mission - I found the issue of Access Advisor. It was December 2001 Advisor Tips "Creating Derived Tables in Jet" Access 2002/200/97 SQL Server lets you create derived tables, which are SELECT statements that appear in the FROM clause of a query. In other words, you can select from the resultset returned by a nested SELECT statement. There is no documented way to accomplish this in a Jet query without using a saved nested query, but there is a, but there is a little-known synatx that makes it possible. The following query runs in Access 97 and uses a derived table named "GIO" which is not a saved object. The derived table must be enclosed in square brackets and terminated with a period: [<Select Staement>]. SELECT Products.ProductName, Products.UnitPrice, Gio.CatAvg, Categories.CategoryName FROM (Products INNER JOIN [SELECT Products.CategoryID, Categories.CategoryName, Avg(Products.UnitPrice) AS CatAvg FROM Categories INNER JOIN Products ON Categories.CategoryID = Products.CategoryID GROUP BY Products.CategoryID, Categories.CategoryName]. AS Gio ON Gio.CategoryID = Products.CategoryID) INNER JOIN Categories ON Products.CategoryID = Categories.CategoryID ORDER BY Products.ProductID; -Giovanni Caruso, via Andy Barron, Contributing Editor Open Northwind DB and paste the listed select statement into the SQL window of a query, save it and then run it to see the results. Don't try to run before saving though because the query design grid will mess it up really bad. I checked the archives of every other access list I know of and other than "Paul's" response to Gustav on AccessD in mid 2001 it is the earliest reference to this syntax I can find. Given that Giovanni's tip was probably submitted months before it was actually published it would be hard to know which was first. It could be coincidence that both of them originated at roughly the same time. But then again, I have seen a lot of tips in newsletters and magazines, from both readers and staff, that seem to arrive shortly after an item was discussed on one list or another! Maybe Giovanni saw the tip here and really wanted one of those t-shirts from Access Advisor! JB -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock Sent: Thursday, May 08, 2003 10:15 AM To: accessd at databaseadvisors.com Subject: Re: [AccessD] SQL in-line subquery Hi Lambert 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. 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..." It was someone called Paul in July 2001 that mentioned it as common knowledge ... /gustav _______________________________________________ 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 -------------- next part -------------- An HTML attachment was scrubbed... URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030508/42031f7f/attachment-0001.html>