Union queries

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

Not sure what this means:

What must a union query have in order to bring two or more queries
together?

Anyone know?

Robert
 
Union queries must have the same number of fields returned, and, sometimes
the same data types for each field. You can cheat though. For example in the
SQL below, both return 3 fields; however, the second select statement will
return ASA2 for the htn field. Actually you don't need the 'AS htn' part at
all. Just make sure that the first Select statement has what you want to see
in the headings.

SELECT Asa.Month, Asa.Year, Asa.htn
FROM Asa
UNION ALL
SELECT Asa.2Month, Asa2.Year, "ASA2" AS htn
FROM Asa2
ORDER BY 1, 2;

Also there is a difference between UNION and UNION ALL. Plain old UNION will
remove any duplicate records. This takes extra effort and time. If you aren't
worried about duplicates or know for sure that there can't be any, a UNION
ALL is faster.
 
Robert said:
Not sure what this means:

What must a union query have in order to bring two or more queries
together?
Click to expand...


The same number of fields in the same order. If you have
fewer field in one source query, then add a constant
expression of the appropriate type. E.g.

SELECT fname, pdate, pamount FROM donors
UNION
SELECT lname, Null, 0 FROM Pledges
UNION
SELECT "Anonymous", #1/1/07#, ramount FROM trusts

Each column in all the source tables/queries must also have
related types (e.g. not text in one source query and a
number in another).
 
Thank you.

Jerry Whittle said:
Union queries must have the same number of fields returned, and, sometimes
the same data types for each field. You can cheat though. For example in
the
SQL below, both return 3 fields; however, the second select statement will
return ASA2 for the htn field. Actually you don't need the 'AS htn' part
at
all. Just make sure that the first Select statement has what you want to
see
in the headings.

SELECT Asa.Month, Asa.Year, Asa.htn
FROM Asa
UNION ALL
SELECT Asa.2Month, Asa2.Year, "ASA2" AS htn
FROM Asa2
ORDER BY 1, 2;

Also there is a difference between UNION and UNION ALL. Plain old UNION
will
remove any duplicate records. This takes extra effort and time. If you
aren't
worried about duplicates or know for sure that there can't be any, a UNION
ALL is faster.
Click to expand...
 
Thnk you.
Marshall Barton said:
The same number of fields in the same order. If you have
fewer field in one source query, then add a constant
expression of the appropriate type. E.g.

SELECT fname, pdate, pamount FROM donors
UNION
SELECT lname, Null, 0 FROM Pledges
UNION
SELECT "Anonymous", #1/1/07#, ramount FROM trusts

Each column in all the source tables/queries must also have
related types (e.g. not text in one source query and a
number in another).
Click to expand...
 
Back
Top