Union Query Question

[Dalan]

From reading Access 97 help text, it seems that to do what I need to
do will require a Union Query. As this would be my first, I think I
might require a little guidance.

I have two tables with 10 fields that have like data (for instance
both have an item description field, an item price field, a general
notes field, etc.) but with different field names. The tables have
approximately 20/40 other fields that are dissimilar and not needed
for the query. Access help indicated that in order to do a Union Query
each table must have an identical number of fields. So, is the
creation of two new tables, or two selection queries a requirement in
order to satisfy, or are there other workarounds?

I assume somewhere there is an option to select an existing field name
or create a new one? Would there be any other issues to focus on?

Thanks for your help.

[paii]

Each query in the union must return the same number and order of fields, the
tables do not have to match. You can use a calculated field to fill-in data
for fields the underling table is missing. Also look-up the difference
between UNION and UNION ALL to ensure you get all the records you need. The
easiest way to build the UNION is to create each query separately then cut
and past between the SQL windows.

"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

From reading Access 97 help text, it seems that to do what I need to
do will require a Union Query. As this would be my first, I think I
might require a little guidance.

I have two tables with 10 fields that have like data (for instance
both have an item description field, an item price field, a general
notes field, etc.) but with different field names. The tables have
approximately 20/40 other fields that are dissimilar and not needed
for the query. Access help indicated that in order to do a Union Query
each table must have an identical number of fields. So, is the
creation of two new tables, or two selection queries a requirement in
order to satisfy, or are there other workarounds?

I assume somewhere there is an option to select an existing field name
or create a new one? Would there be any other issues to focus on?

Thanks for your help.

[dogwalker]

you already have the tables (so you don't need new tables)
you can use two queries but you don't have to.
the fieldnames have to be the same.
look at the examples in the help (they use the AS keyword)
also consider the ALL keyword (UNION ALL SELECT)
(or you may get less records than you expect)
"Dalan" <ot***@safe-mail.net> wrote in message
news:50**************************@posting.google.c om...

From reading Access 97 help text, it seems that to do what I need to
do will require a Union Query. As this would be my first, I think I
might require a little guidance.

I have two tables with 10 fields that have like data (for instance
both have an item description field, an item price field, a general
notes field, etc.) but with different field names. The tables have
approximately 20/40 other fields that are dissimilar and not needed
for the query. Access help indicated that in order to do a Union Query
each table must have an identical number of fields. So, is the
creation of two new tables, or two selection queries a requirement in
order to satisfy, or are there other workarounds?

I assume somewhere there is an option to select an existing field name
or create a new one? Would there be any other issues to focus on?

Thanks for your help.

[Pieter Linden]

You need to make sure you alias the field names so the aliases/names
match... Say you have two tables that are union-compatible (field
types are the same).

TableA(Field1, Field2, Field3)

TableB(FieldA, FieldB, FieldC)

SELECT TableA.Field1 As FieldA, TableA.Field2 As FieldB, TableA.Field3
As FieldC
FROM TableA
UNION ALL
SELECT FieldA, FieldB, FieldC
FROM TableB...

then you can query that result... but NOTE one thing... You CANNOT
update it. (because there's no way to find the original record's table
from a union query).