Union Query

[PatK]

Hi...hope someone has an idea about how to do this easily.

I have two tables. Table1 has ~75 fields/columns. Table 2 has ~50
fields/columns. The two tables have columns named the same (I would estimate
approximately 30 fields have common names). While there are common fields,
the are not "in order" ie, field for field match, starting with column 1.
Example:

Table 1:
Field A Field B Field C Field D Field E

Table 2:
Field A Field 1 Field E Field 2

I want to create a query that, once run, I will have (not necessarily in any
order):

Field A Field B Field C Field D Field E Field 1 Field 2

A record from Table2, once the union occurs, would have "blank or null" data
in those fields not in common with Table 1 (and vice versa).

Note: Field A would be primary key, and unique in the output table. In
other words, Table2 would not have a row with the same Field A value in it,
as Table1.

I am about to embark on creating a monsterously long query of Tables 1 and
2, to get two new queries that have "aligned" rows, then do a union on them,
once I get all the columns perfectly aligned and filled with blank/null data,
as need, so the union will work. But I figure...gosh...there must be an
eaiser way.

Thanks...trying to do this a bit more elegantly than my noobiness allows.

Thanks!

patk

 

[Rob Parker]

Hi Pat,

The first thing that strikes me from this post is that I suspect that your
data is not normalised, and you are storing data in field names. It is rare
that tables in a normalised Access database need anywhere near that number
of fields/columns; Access tables are "long and thin" rather than "short and
wide". If, for example, you have fields such as Student, Class1, Class2,
Class3 ..., you are "committing spreadsheet" on your tables; for that data,
you should have a table with only 2 fields: Student and ClassName.
Normalised data will make life much simpler when you subsequently write
queries to base forms and reports on. If you are not familiar with the
concept of normalised data, check out some of the references here:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

Having said that, here's the essentials of how to build a Union query. You
can build each SELECT section in the query design grid, but you will have to
switch to SQL view to build the Union query itself, cutting/pasting each
SELECT section and typing the "UNION " or "UNION ALL " between each SELECT
subquery.

A Union query has the general structure:
SELECT Field1, Field2, ... FROM Table1
UNION (or UNION ALL)
SELECT FieldA, FieldB, ... FROM Table2
UNION
... ;

Each SELECT subquery may have other clauses (eg. WHERE ...)

UNION will prevent duplicate records appearing, if they exist in the SELECT
subqueries; UNION ALL will include any duplicates. UNION ALL also runs
faster, since the query engine does not have to check for duplicates.

Each SELECT subquery must have the same number of fields; the UNION query's
fieldnames will be the fieldnames from the first SELECT subquery, and fields
from following SELECT subqueries will be placed in fields in the order in
which they occur. Corresponding fields must also have the same datatype.
You can use aliases in the first SELECT subquery to define the fieldnames in
your output, and you use Null as a fieldname to force a null entry for a
field which does not exist in one of the SELECT subqueries. Having the same
field names in each table does not force the fields to match in the Union,
the only thing that matters is the order in which the fields appear in each
SELECT subquery. For example:

Assume Table1 has the following structure and data:
Field1 Field2 FieldZZZ
23 abc xyz
42 qqq vvv

and Table2 has the following structure and data:
Field2 FieldB
99 test
100 again

The following Union query:
SELECT Field1 AS NewFieldA, Null AS NewFieldB, Field2 AS NewField3,
FieldZZZ
FROM Table1
UNION ALL
SELECT Field2, FieldB, Null, Null
FROM Table2;
would combine the data from Table1 and Table2 to give:
NewFieldA NewFieldB NewField3 FieldZZZ
23 (Null) abc xyz
42 (Null) qqq vvv
99 test (Null) (Null)
100 again (Null) (Null)

Again, before you embark on setting up a horrendously large Union query, I
would urge you to ensure that your data is normalised. You will save
yourself countless problems (and make your life much quicker and easier) if
you do so. Indeed, you may find that, when you set up normalised tables and
populate them via append queries from your existing tables, that your need
for a Union query disappears.

HTH,

Rob

 

[PatK]

Gosh, Rob...great answer. Thanks for taking all the time to go through it.
It is, as I fear, from the perpective of having to use Null, etc to plug in
the "missing" fields as they occur.

I definitely understand you comment about normalization. In fact, have done
a lot of this already. In this particularly case, for legal purposes, we
need to keep the two original tables intact. However, for "real" work to be
done with the data, I need to do something else from a transformational
perspective. What happens with the "union" table is that it get's linked to
many Excel spreadsheets used by many folks around the world. One persons
wants the first 10 fields, plus the 17th. The next person uses fields 20-25.
So, they link this to Excel 2007 pivots. Thus far, it is the simplest way
we have been able to find to do this, without creating custom tables/queries
for 200 different users.

From my end, I definitely do normalize, and create just the tables (long and
thin) that you suggest, else, as you note, life get's ugly, fast.

However, do have one additional question. As you have shown in your sample
union example, you inserted Null values, etc, as needed. How does this
affect the data types? For example, let's say your NewField B is intended to
be a date field, but the first data element I insert is a Null, in the first
row. I realize a date can have a null value, but what defines the "type" of
field for that row? The null value, or a subsequent date value that comes in
from the second table?

Probably a very dumb question, so I apologize in advance!

Thanks again!

PatK

 

[Rob Parker]

Hi Pat,

Yes, sometimes real-world requirements make things much more difficult than
they should be ;-)

As for your additional question: if the first SELECT subquery has fields
populated with a null entry, the field will take the datatype of the first
actual data it finds. On testing, I find that things are not exactly as I
stated - if you union a text field with a number field, it will convert the
number to a text string (and you therefore will not be able to perform
mathematical operations on it, and will get strange results if you use > or
< comparison operators, etc). And, if you attempt to union a datetime field
with a different datatype, you will get a "Data type mismatch in criteria
expression" error.

Again, HTH,

Rob

 

[PatK]

Thanks, Rob...I got it working just fine (ugly, but fine!). Thanks again!

pat