Drew Wutka
DWUTKA at marlow.com
Tue May 6 16:49:35 CDT 2003
Oh, I see. The help files do show how to use them as a field or in Where clauses.... Drew -----Original Message----- From: Charlotte Foust [mailto:cfoust at infostatsystems.com] Sent: Tuesday, May 06, 2003 4:25 PM To: accessd at databaseadvisors.com Subject: RE: [AccessD] SQL in-line subquery You can use an in-line subquery as if it were a table or a saved query: SELECT T1.* FROM Table1 AS T1 INNER JOIN [SELECT DISTINCT Table2.ID FROM Table2]. AS T2 on T1.ID = T2.ID; Does that explain it? You have to use the square brackets followed by a period to wrap the in-line subquery. If you ever find it in help, for goodness sake, post the reference here! Charlotte Foust -----Original Message----- From: Andy Lacey [mailto:andy at minstersystems.co.uk] Sent: Tuesday, May 06, 2003 11:37 AM To: accessd at databaseadvisors.com Subject: RE: [AccessD] SQL in-line subquery Can I second that question? As you say, looking up subqueries in help doesn't tell me. What do you mean? Andy Lacey http://www.minstersystems.co.uk > -----Original Message----- > From: accessd-bounces at databaseadvisors.com > [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka > Sent: 06 May 2003 17:30 > To: 'accessd at databaseadvisors.com' > Subject: RE: [AccessD] SQL in-line subquery > > > What's the [...] syntax? > > Drew > > -----Original Message----- > From: Charlotte Foust [mailto:cfoust at infostatsystems.com] > Sent: Tuesday, May 06, 2003 11:19 AM > To: accessd at databaseadvisors.com > Subject: RE: [AccessD] SQL in-line subquery > > > As I recall, the help files did NOT include anything on the > [...] syntax. I stumbled over that myself in 97 but never > found anything in the help files except on regular subqueries. > > Charlotte Foust > > -----Original Message----- > From: Drew Wutka [mailto:DWUTKA at marlow.com] > Sent: Tuesday, May 06, 2003 8:09 AM > To: 'accessd at databaseadvisors.com' > Subject: RE: [AccessD] SQL in-line subquery > > > Actually, 'in-line' queries or subqueries are described in > Access 97's help files. Just look up SQL Subqueries, or just > subqueries. > > Drew > > -----Original Message----- > From: Gustav Brock [mailto:gustav at cactus.dk] > Sent: Tuesday, May 06, 2003 10:12 AM > To: accessd at databaseadvisors.com > Subject: Re: [AccessD] SQL in-line subquery > > > Hi Jim > > > <Snip> > > Those not familiar with the strange [..]. syntax may look up the > > archives for "SQL in-line subquery" <Snip> > > > Since I was (am) unfamiliar with this concept, I did look it up. I > > have a few questions. > > > 1. Does this method run faster than a nested query? > > A saved and compiled nested query may run faster. Haven't > done any testing though. > > > 2. What are the pitfalls? > > One is that no documentation is available as far as I know. > > > 3. What are the benefits? > > That you can avoid temporary tables or subqueries. All code > is in one query which is an advantage if you build it from VBA code. > > /gustav > > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/a> ccessd > Website: > http://www.databaseadvisors.com > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/a> ccessd > Website: > http://www.databaseadvisors.com > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/a> ccessd > Website: > http://www.databaseadvisors.com > _______________________________________________ > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/a> ccessd > Website: > http://www.databaseadvisors.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