[AccessD] Undocumented Access SQL syntax - Derived tables

We've discussed this trick before in the list.  I found it back in A97,
where it was problematic and had to be used with care, and I've used it
in 2k and XP.  There was a tip on this in the December 2001
Access-VB-SQL Advisor magazine, called "Create Derived Tables in Jet".
As I recall, they are covered in O'Reilly's Access Database Design &
Programming as well.

Charlotte  Foust


-----Original Message-----
From: Michael Maddison [mailto:michael at ddisolutions.com.au] 
Sent: Thursday, July 29, 2004 8:15 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] Undocumented Access SQL syntax - Derived tables


Hi list,

I've been mainly working in SQL 2K for a while now and have grown to
love using derived tables in my sql statements.  A shame you can't do
the same in Access! You thought so, well so did I.  A colleague came
across a website (no link,sorry) which shows how to do derived tables in
MS Access.

Undocumented Syntax
SELECT .* 
FROM
[SELECT sum(x) FROM FOO]. BAR

***Notice the [] and the space after the .

This works like a charm, even the query designer likes it!  The only
drawback I've found (apart from being undocumented) is the derived
tables cannot have column names with square brackets arround them. So 
	SELECT .* 
	FROM
	[SELECT sum([My Badly Named Field]) FROM FOO]. BAR
Will not parse correctly.

Try it out.

cheers

Michael M
-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com