UNION QUERIS

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

Robert Raley

Thanks for the help. The problem is that I have not all columns are equal
in the queries. While query 1 returns certain data query 2 returns other
data. Some columns are the same some are different. I seem to be getting
error messages that there are not the same number of columns

Advise is welcome :-)

Regards

Bob
 
then you have a serious problem
in a union query, both queries MUST have the same number
of columns and both queries MUST have the same column
headers or aliases.
otherwise crash/burn/error messages
 
Ok great. Thanks for the encouragement. Now how do I solve my problem.

Thanks Bob
 
Hi,


You can complement the missing columns with NULLs or constants.

Not only the columns must be equal, in number, but the "vertically
welded" columns must be "compatible" too. As example, with Jet, a varchar
(string) column f1 and a numerical column f2 will result into a varchar (or
string, if you prefer):


SELECT f1 FROM table1 UNION ALL SELECT f2 FROM table2.



An example with NULL:

SELECT f1, f2, f3, NULL, NULL FROM myLeft
UNION ALL
SELECT NULL, NULL, NULL, g1, g2 FROM myRight



(that result is called a JOIN UNION)



Sure, we can also, just for the show, use:

SELECT f1, f2, f3 FROM myTable1
UNION ALL
SELECT g1, g2, NULL FROM myTable2



or anything we may need, in the same spirit.






Hoping it may help,
Vanderghast, Access MVP
 
Thanks for the help. The first option worked like a charm with the
exception that for the header it puts exp1006 etc for the header. I need to
know how to change that to the name of the field or the name of something
that I can use in a report.

This is what is reported
pay_start_date pay_end_date Expr1002 mod_stats_Id stage_name
Name Expr1006 Expr1007 Expr1008 Expr1009 shifts_worked Shift_rate
Tot_shift_pay

This is WHAT I NEED
pay_start_date pay_end_date trm mod_stats_Id stage_name Name min
conv pay totpay shifts_worked Shift_rate Tot_shift_pay


The second option that you gave me tell me that I do not have the same
number of columns.

Thanks agian for your help.

Bob
 
Hi,


You can supply AS for aliases in the FIRST SELECT statement:

SELECT f1, f2, f3 AS nf3, NULL As f4, NULL As f5 FROM myLeft
UNION ALL
....


The fields of the result would then be: f1, f2, nf3, f4, f5

(You can alias the existing fields).



If there is an error message telling you don't have the right number of
columns, add a NULL or two, or three, as required, to the SELECT that has
one, or two, or three columns less than the other SELECTs.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top