Union Query

[rml]

Can anyone show or tell me the basic steps for creating a union query? I'm
trying to combine two queries by the PN field.

Thanks.

 

[John Spencer]

First of all, do you really want a UNION query? A union query "stacks" the
records from source one onto records from source two.

So, if you have records a,b, and c in source one and records d,e,f in source
two, you end up with six records
a
b
c
d
e
f

If that is what you want, then assuming your two queries have the same number
of fields and each set of fields (field 1 from query 1 and field 1 from query
2) is of the same data type, you can make a union query fairly easily.

-- Open one of the queries and switch to SQL view. Copy the SQL
-- Make a new query with no table and no fields
-- Select Query: SQL specific : Union from the menu
-- Paste the query and remove the trailing semi-colon
-- Type the following at the end of the text
UNION ALL
-- Open the other query in SQL view and copy the SQL statement
-- Paste that after Union All

If you want to eliminate duplicate records in the Union query, then change
UNION ALL to UNION.

Oh I forgot to mention, if the source queries contain an ORDER BY (Sort)
remove the ORDER BY clause. If you want to impose a sort order you will need
to add it to the very end of the UNION query. Use the field names from the
first query to specify the sort.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[rml]

Not sure then. I want to combine the records.

I want to say take the 50 records from query one and add them to the 50
records in query two. That would give me a total of 100 records. Both
queries have the same number of fields. Actually, I'm combining four
queries. If not union then what would you recommend?

Thanks.

 

[John Spencer]

Then you do want a union query.

You should be able to follow the instructions I gave and just add the other
two tables

So you would end up with something like:

SELECT A, B, C, D
FROM TableA
UNION ALL
SELECT A, C, Y, Z
FROM TableB
UNION ALL
SELECT A, x, y, z
FROM TableC
UNION ALL
SELECT A, B, C, D
FROM TableD

Try it, UNION queries don't destroy any data.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[rml]

That worked! Another quick question. How would I format say field a and b
to currency?

Thanks.

 

[rml]

And also sort (asending)

Then you do want a union query.

You should be able to follow the instructions I gave and just add the other
two tables

So you would end up with something like:

SELECT A, B, C, D
FROM TableA
UNION ALL
SELECT A, C, Y, Z
FROM TableB
UNION ALL
SELECT A, x, y, z
FROM TableC
UNION ALL
SELECT A, B, C, D
FROM TableD

Try it, UNION queries don't destroy any data.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[John Spencer]

Sorting is done at the end of the union, using the field names in the first query.

SELECT A, B, C, D
FROM TableA
UNION ALL
SELECT A, C, Y, Z
FROM TableB
UNION ALL
SELECT A, x, y, z
FROM TableC
UNION ALL
SELECT A, B, C, D
FROM TableD
ORDER BY C, D


Formatting can get tricky. I would normally apply formatting using a control's
format property on a form or report. If you format in the Union query you
would have to use the format function AND that would change the value type
into a string for the column. So, if you formatted column A as currency using
the format function and attempted to sort by that column, you would get a text
sort (1,10,100,12,2,3,5) instead of a numeric sort (1,2,3,5,10, 12,100).

SELECT Format(TableA.A,"Currency") as A, b, c, d
FROM TableA
Union All ...

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

 

[rml]

Perfect! Thanks for your help.

Sorting is done at the end of the union, using the field names in the first query.

SELECT A, B, C, D
FROM TableA
UNION ALL
SELECT A, C, Y, Z
FROM TableB
UNION ALL
SELECT A, x, y, z
FROM TableC
UNION ALL
SELECT A, B, C, D
FROM TableD
ORDER BY C, D


Formatting can get tricky. I would normally apply formatting using a control's
format property on a form or report. If you format in the Union query you
would have to use the format function AND that would change the value type
into a string for the column. So, if you formatted column A as currency using
the format function and attempted to sort by that column, you would get a text
sort (1,10,100,12,2,3,5) instead of a numeric sort (1,2,3,5,10, 12,100).

SELECT Format(TableA.A,"Currency") as A, b, c, d
FROM TableA
Union All ...

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County