when or Where do you use a union query ?

[shaun palmer]

when or Where do you use a union query ?
how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).

Your help would be greatly
appreciated. Thank you.

['69 Camaro]

Hi, Shaun.

You can only use a UNION query in the SQL View pane or in VBA code, since
the QBE design grid is not capable of displaying the elements of a UNION
query.

The general syntax is:

SELECT *
FROM tblPopulation
UNION
SELECT *
FROM tblcustomer
GROUP BY <group_criteria>
HAVING <aggregate_criteria>
ORDER BY <column_criteria>;

A UNION query has the following restrictions:

1.) The number of fields in the field list of each SELECT clause must be
the same.

2.) The sequence of the field names in each SELECT clause must correspond
to similar fields. The data types of each of these similar fields doesn't
have to match, but if they don't, you could wind up with unintended results.
Also, if a corresponing field's data type in one SELECT clause is numeric
and the other SELECT clause's field's data type is text, then the resulting
field in the data set will be text.

3.) Only one ORDER BY clause is allowed, and it must be used in the last
UNION SELECT statement.

4.) GROUP BY and HAVING clauses can be used in both SELECT statements.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"shaun palmer" <sh**********@ihug.co.nz> wrote in message
news:a0*************************@posting.google.co m...

when or Where do you use a union query ?
how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).

Your help would be greatly
appreciated. Thank you.

[Nicole]

sh**********@ihug.co.nz (shaun palmer) wrote in message news:<a0*************************@posting.google.c om>...

when or Where do you use a union query ?

how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).

A UNION query is used when you need to combine the data from two
separate queries. They may or may not be in the same table(s). For
example, if you wanted to create a Christmas card list, you might
combine the names and addresses in the vendor table AND the client
table, without joining the data in any way.

[Pieter Linden]

sh**********@ihug.co.nz (shaun palmer) wrote in message news:<a0*************************@posting.google.c om>...

when or Where do you use a union query ?
how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).

Your help would be greatly
appreciated. Thank you.

IF you don't know anything about union queries, you should probably
make friends with the Access help files. They're for returning a
single resultset from two or more union-compatible tables/queries.

You can't use * when creating union queries - you can alias your
fields any way you want, though.

['69 Camaro]

Hi, Pieter.

You certainly can use the * when writing UNION queries, but restrictions
apply as outlined in my first post:

1.) The number of fields in the field list of each SELECT clause must be
the same.

2.) The sequence of the field names in each SELECT clause must correspond
to similar fields. The data types of each of these similar fields doesn't
have to match, but if they don't, you could wind up with unintended results.
Also, if a corresponding field's data type in one SELECT clause is numeric
and the other SELECT clause's field's data type is text, then the resulting
field in the data set will be text.

Since a * in the SELECT clause means "all fields," the data structure of
both tables needs to be identical, although the actual names of the fields
can be different. For example, if tblPopulation has the following fields:

Pop_ID (Primary Key)
Pop_F_Name Text
Pop_L_Name Text
Pop_Address Text
Pop_City Text
Pop_State Text

and the tblcustomer has the following fields:

Cust_ID (Primary Key)
FirstName Text
LastName Text
Address Text
City Text
State Text

then the following UNION query of these two tables:

SELECT *
FROM tblPopulation
UNION
SELECT *
FROM tblcustomer
ORDER BY 3;

will produce a data set of the union with the field names of the
tblPopulation table, sorted by the values in the resulting Pop_L_Name
column, the third column. There's no need to alias the field names of the
tblcustomer table, as long as the corresponding fields were created in the
same sequence (or repositioned in design view afterwords) as the similar
corresponding fields in the tblPopulation table.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.
"Pieter Linden" <pi********@hotmail.com> wrote in message
news:bf**************************@posting.google.c om...

sh**********@ihug.co.nz (shaun palmer) wrote in message

news:<a0*************************@posting.google.c om>...

when or Where do you use a union query ?
how can I wright sql,
tblPopulation,* tblcustomer,* one to one with
all the appropriate attributes(field).

Your help would be greatly
appreciated. Thank you.

IF you don't know anything about union queries, you should probably
make friends with the Access help files. They're for returning a
single resultset from two or more union-compatible tables/queries.

You can't use * when creating union queries - you can alias your
fields any way you want, though.