UNION ALL headache

[Dogbert]

Hello all,

I have an access query which I'm having trouble with. The query runs but the numbers that come out don't seem to make logical sense.

The query (with query and field names substituted) is as follows:

SELECT sum(currencyField) from
A

UNION ALL

select sum(currencyField) from
B

UNION ALL

Select sum(currencyField) from
(select * from A union all select * from B);

The query then outputs something along the lines of (actual values are substituted):

1.70
0.30
1.90

Now 1.90 is less than 2.00 - In other words the total value of two discrete tables is greater than the value of those two tables after a union all. What am I doing wrong?

Thanks,
D.

 

[Banana]

Try it without ALL keyword.

I think the value for each table is higher than it is actually because of some rows may get counted twice; UNION ALL.

 

[Dogbert]

Try it without ALL keyword.

I think the value for each table is higher than it is actually because of some rows may get counted twice; UNION ALL.

Thank you for the quick reply. Union (without all) results in the same problem.

The tables are distinct and the ALL is in there just as a matter of habit.

 

[Banana]

Hmmm....interesting....


Ok, let's start out with simple.

First, create two simple (temporary) queries for each table to sum the currency field. Note the sum. Does they match the first UNION query? If you added the two sums together, do you get 2.00 again, or something else?

 

[Dogbert]

Thank you again Banana.

I have finally solved my problem. I made a silly mistake.

To illistrate the root of the problem I have created a hypethetical structure for table A and table B that would cause this issue:

Table A:

CurrencyField . . . OtherField
1.7 . . . . . . . . . . 0

Table B

OtherField . . . . . . CurrencyField
0.2 . . . . . . . . . . . 0.3

Because UNION works on the order of the columns and not their names it adds the OtherField in table B to the CurrencyField in table A giving a total of 1.9 rather than 2.

Thanks again for being a sounding board to help me work through this - very greatful.

D.

 

[Banana]

Glad to know we got it sorted it out!