where to find union query sql

[S.Hoitinga]

Hi

Can anyone please explain to me what the function (besides compact overview)
of a union-query could be.
Can onyone pelase tell me where to find instructions on how to write sql for
the union query.

thank you.

Sybolt

 

[Steve Huff]

What the function is - as in an example? Well let's say you have a table
with suppliers addresses and a table with customer addresses and you want a
report to list all of them, for the purpose of sending them Christmas cards
. You could UNION the two tables together and report on them as one big
happy family like this:

SELECT [CompanyName], [City]
FROM [Suppliers]

UNION SELECT [CompanyName], [City]
FROM [Customers]
ORDER BY [City];


If you search for UNION in Access Help you will find plenty of information
on it's syntax.

 

[S.Hoitinga]

Thanks Steve,

In the Northwind database that ships with Access there is a union query.
In the SQL there is a syntax that uses select fields as relation. I didn't
understand that one.
Your example is quite a lot simpler.
The general idea is that fields in different tables have exactly the same
name, I suppose.

Any idea why there is no graphical interface to create unionquery's?

greets

Sybolt

 

[John Vinson]

The general idea is that fields in different tables have exactly the same
name, I suppose.

The field *names* can be the same or different, it makes no
difference. However you must have the same number of total fields, and
they must match in datatype and size: that is if you select a Long
Integer Number, a Text, and a Date in one of the SELECT clauses of the
UNION, you must select a Long Integer Number, a Text, and a Date in
each of the SELECTs.

Any idea why there is no graphical interface to create unionquery's?

They're an uncommonly used and rather advanced technique. The query
grid is simply a user friendly tool to create SQL strings; the SQL is
"the real query". Since a UNION query can be quite complex (you could
have a dozen tables with different fieldnames and criteria) and since
most uses of a UNION presuppose that you're intentionally using an
advanced tool, the GUI interface would be more trouble to build and
use than it would be worth - or so I read Microsoft's attitude.

John W. Vinson[MVP]

 

[S.Hoitinga]

John,

Thanks a lot.
I now know how to create them.
I also added a new field (using as) to let you know for instance the table a
record comes from.

For my purposes I know enough at the moment.

Greets,

Sybolt