[AccessD] Access 2000 - Union query

Stuart

Gustav mentioned a posting in the archives, I found the following:
===============================================
SenderName:Gustav Brock
Posted:7/16/2002 12:01:58 PM
Subject:Re: [AccessD] SQL in-line subquery (solved)

Post Text:

Hi Paul

Where on Earth did you obtain this magic syntax??

Maybe not from day 1 but perhaps from day 111 of my coexistence with
Access I have looked for a way to include a subquery as a recordsource -
returning more than one record - in a query. The on-line help doesn't
mention it, and I believed it to be truly impossible in Access.

For those still not seeing the light, here is the general syntax for
Paul's request (a typical need and use for a subquery):

SELECT
Count(*) AS DaysUnique
FROM
[SELECT DISTINCT Days FROM tblTable]. AS subDistinctDays;

Here, DaysUnique will appear as the field name and - in design mode -
subDistinctDays will appear as the name of the recordsource (normally a
table or stored query).

The very basic syntax is:

SELECT
*
FROM
[SELECT * FROM tblTable;]. AS subDistinctDays;

The magic is the brackets around the subquery and the dot as suffix:
[subSQL].

This works even for Access 2.0 (tried just for fun) although here the
fieldnames of the subquery are not shown.

Thanks Paul! Learned something new today!

/gustav


> Please let me clarify by showing you example below. I'm looking for the
> count of distinct records for Pickup_Date.
>
> Table1:
>
> Pickup_Date
> 1/1/01
> 1/2/01
> 1/2/01
> 1/3/01
> 1/3/01
> 1/3/01
>
> The query results for this field is = 3, which is the count of distinct
> records. I believe I figured this one out after I sent message. Here is my
> query. Please correct me if I'm wrong or better way.
>
> SELECT Count(*) AS Days
> FROM [SELECT tblData.PDate
> FROM tblData
> GROUP BY tblData.PDate]. AS Days;
===============================================
===============================================


I also found a small document that discusses it
===============================================
WORKING WITH SUBQUERY IN THE SQL PROCEDURE

www.nesug.org/Proceedings/nesug98/dbas/p005.pdf -

The author states:
"Users have to understand the correct way to use subqueries
in a variety of situations. This paper will explore how subqueries
work properly with different componentsof SQL such as WHERE,
HAVING, FROM,SELECT, SET clause. It also demonstrates when and how
to convert subqueries into JOINs solutionsto improve the query performance."
===============================================

There is some helpful info here

http://www.comp.nus.edu.sg/~ooibc/courses/sql/dml_query_subquery.htm

===============================================


I also found "subqueries" discussed in the three Informix documents
on SQL whose descriptions are below on the following page
http://www-3.ibm.com/software/data/informix/pubs/library/ids_92.html

The links to each of them is also on the above page.


Informix Guide to SQL: Reference, December 1999 (G251-0373-00)
This guide provides information on the following topics:
Informix databases, data types, system catalog tables, environment
variables, and the stores_demo demonstration database.
It also contains a glossary.


Informix Guide to SQL: Syntax, December 1999 (G251-0374-00)
This guide provides detailed descriptions of the syntax for
all Informix SQL and SPL statements.

Informix Guide to SQL: Tutorial, December 1999 (G251-0375-00)
This guide provides a tutorial on SQL, as implemented by Informix
products. It describes the basic ideas and terms that are used
when you work with a relational database.

Dave

===============================================
===============================================
===============================================


----- Original Message -----
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: <accessd at databaseadvisors.com>
Sent: Saturday, February 01, 2003 5:03 PM
Subject: Re: [AccessD] Access 2000 - Union query


> That's neat. Is that "[.]." syntax documented anywhere?
>
> >> or wrap the select/union part as an
> > > in-line (sub)query using the strange "[ .. ]." syntax which I learned
> > > from Paul (look in the archives for subject: SQL in-line subquery):
> > >
> > >   INSERT INTO Table12
> > >     (Field1, Field2)
> > >   SELECT
> > >     Field1, Field2
> > >   FROM
> > >     [SELECT
> > >       Field1, Field2
> > >     FROM
> > >       Table1
> > >     UNION ALL
> > >     SELECT
> > >       Field1, Field2
> > >     FROM
> > >       Table2;].
> > >     AS subQuery;
> > >
> > > UNION ALL will select all records from the two tables.
> > > Omit ALL if you wish distinct records only.
> > > The name subQuery can be nearly anything except reserved words.
> > >
> > > /gustav
> > >
> > > _______________________________________________
> > > AccessD mailing list
> > > AccessD at databaseadvisors.com
> > >  http://databaseadvisors.com/mailman/listinfo/accessd
> > > Website: http://www.databaseadvisors.com
> > >