UNION query splitting columns

[Allen Browne]

The ideal solution would be to create a single column for the 4 types of
expense, instead of 4 tables. Just include an extra column where the user
chooses the ExpenseType (one of 4 values.) You will find this is much easier
to work with, and much faster. For example, a crosstab query would give you
the report you want (journal-style, with a column for each expense type.)

If you can't do that, you will need to fill the other columns with Null,
e.g.:
SELECT ExpenseID,
ExpenseAmount AS Type1,
IIf(False, 0,Null) AS Type2,
IIf(False, 0, Null) AS Type3,
IIf(False, 0, Null) AS Type4
FROM ExpenseTable1
UNION ALL
SELECT ExpenseID,
IIf(False, 0,Null) AS Type1,
ExpenseAmount AS Type2,
IIf(False, 0, Null) AS Type3,
IIf(False, 0, Null) AS Type4
FROM ExpenseTable2
UNION ALL
....

The IIf() expression is necessary so Access understands the column is
numeric. Of course False will never be True, but the presence of the
alternative is enough to let JET know that this is a Number.

 

[Allen Browne]

Actually, you probably need Currency here, with cents (not whole numbers),
so you would need:
IIf(False, CCur(0),Null) AS Type2,
etc.

 

[Syed Zeeshan Haider]

Hello Everybody,
I am using UNION query to summarize sums of expenses from 4 different table.
The problem is all the results are being displayed in one columns of four
rows. I want every result to appear in a different column. Any ideas?

 

[Douglas J. Steele]

Just curious about why you're including the IIf statement, Allen.

Wouldn't

SELECT ExpenseID,
ExpenseAmount AS Type1,
CCur(0) AS Type2,
CCur(0) AS Type3,
CCur(0) AS Type4
FROM ExpenseTable1
UNION ALL
SELECT ExpenseID,
CCur(0) AS Type1,
ExpenseAmount AS Type2,
CCur(0) AS Type3,
CCur(0) AS Type4
FROM ExpenseTable2
UNION ALL

be sufficient (although with how you're using IIf, you've got the equivalent
of)

SELECT ExpenseID,
ExpenseAmount AS Type1,
Null AS Type2,
Null AS Type3,
Null AS Type4
FROM ExpenseTable1
UNION ALL
SELECT ExpenseID,
Null AS Type1,
ExpenseAmount AS Type2,
Null AS Type3,
Null AS Type4
FROM ExpenseTable2
UNION ALL

 

[Allen Browne]

Numerically, that sums the same, of course, Doug.

But Null is not the same as zero, and I believe Null is the most appropriate
value for this data. The IIf() expression allows you to get the Null value
in your column without the subsequent data from the SELECTs in the UNION
being treated as fields of type Text or Binary.

 

[Guest]

From your description I suspect you don't need a UNION query here at all, but
a JOIN. The simplest approach would probably be to create four queries, one
based on each table, each summing the values form that table, and then join
the four queries in another query to return the sums from each as a separate
column.

How you join the four queries would depend on how each groups the values, if
at all. If they are ungrouped, each returning just one row then you don't
need to explicitly join them in the final query as only one row will be
returned. If they are grouped by month for instance, then you'd join them on
the year and month columns (or just month if only one year is involved). You
might need to use OUTER JOINS if each of the four queries does not return
rows for every month.

An alternative approach, using a single query, would be to include four
subqueries in a query's SELECT clause, each subquery aggregating the values
from one of the four tables and correlated in its WHERE clause with the outer
query, e.g. if the outer query returns one year/month value per row then each
subquery would be correlated on the year and month to give the summary
expenses for that year/month only).

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

Yes, I agree that Null is appropriate. My point, though, was that you're
suggesting using

IIf(False, CCur(0), Null) As Type2,

Since your condition is False, that will always evaluate to Null. What
advantage is there to including the IIf statement?

 

[Allen Browne]

In a UNION query, the data type of each column is determined by the data
type of the columns in the first SELECT.

If we just used:
SELECT ExpenseAmount AS Type1, Null AS Type2, ...
Access would be unable to determine the data type of the column, so the
field would be binary data. You can verify this by saving the query, and
then trying this in the Immediate Window:
? Currentdb.QueryDefs("Query1").Fields("Type2").Type = dbBinary
The binary type gives incorrect results when you try to treat the value as
numeric further down the query.

So, how do you tell JET the data type of the column when every row contains
Null? If JET has some alternative to evaluate, it is able to determine the
column's intended data type. So, the mere presence of an alternative (even
though the alternative is never used) does the trick.

In summary, the expression:
IIf(False, CCur(0), Null) As Type2
instructs JET to treat the column as a Currency data type, even though all
values are Null. Since this is the first SELECT, when the later SELECTs in
the UNION put Currency data into the column, the query gives correct
results.

To illustrate the issue, create a Table1 with 1 record, and compare the
results of these 2 queries:

SELECT 0 AS F1, Null AS F2 FROM Table1;
UNION
SELECT Null AS F1, 65 AS F2 FROM Table1;

SELECT 0 AS F1, IIf(False, 0,Null) AS F2 FROM Table1;
UNION
SELECT Null AS F1, 65 AS F2 FROM Table1;

 

[Douglas J. Steele]

Thanks, Allen. I'd forgotten that it wasn't necessary that the field be used
to set the field type.

 

[i_takeuti]

Hello Everybody,
I am using UNION query to summarize sums of expenses from 4 different
table. The problem is all the results are being displayed in one columns
of four rows. I want every result to appear in a different column. Any
ideas?

 

[Syed Zeeshan Haider]

Worked like magic! I do not have enough words to thank you. I wish you all
the best.