SQL query with UNION ALL of 2 tables does not work

[leifoet]

The purpose is to calculate the Total-IN and the Total-OUT of the UNION tables A and B where the summed records correspond to a certain ID = 100.
The UNION query below only gives the totals of the matching records from table A (= 1st part of the query up to UNION) - and does not add any matching record from table B (= 2nd part of the query after UNION).
How can I make this code work correctly?
Thanks.

SQL="SELECT "&_
"(Select Sum(Out) from Table_A where ID=100) AS TotOUT, "&_
"(Select sum(In) from Table_A where ID=100) AS TotIN "&_

"FROM Table_A "&_
"WHERE (ID=100) "&_ 

"UNION ALL SELECT "&_
"(Select Sum(Out) from Table_B where ID=100) AS TotOUT, "&_
"(Select sum(In) from Table_B where ID=100) AS TotIN "&_

"FROM Table_B "&_
"WHERE (ID=100)"

 

[Andrzejek]

Instead of imbedded SELECTs, wouldn't be easier to do:

[pre]
SQL = [blue]"SELECT " & _
" Sum(Out) AS TotOUT, " & _
" Sum(In) AS TotIN " & _
" FROM Table_A " & _
" WHERE ID =[/blue] [red]100 "[/red] & _
" UNION ALL " & _
[green]" SELECT " & _
" Sum(Out) AS TotOUT, " & _
" Sum(In) AS TotIN " & _
" FROM Table_B " & _
" WHERE ID =[/green][red] 100" [/red]
[/pre]


When you run "2nd part of the query after UNION" separately, do you get any records?


---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

Thanks Andrzejek, but this query gives the same wrong result as mine : only the TotOUT and TotIN of table A are shown - the matching records of table B are not included.
If only the code lines from table B are executed (= code lines up to and including UNION removed), now the end result shows the TotIN and TotOUT of table B.

More tips to combine A and B?

 

[Andrzejek]

this query gives the same wrong result as mine " - OK, so what do you get if you just run the [blue]BLUE[/blue] Select?
And what do you get when you run just the [green]GREEN[/green] Select?

Show the output of both, please.

Should be something like:[blue][pre]
TotOUT TotIN
1234 76546[/pre][/blue]
[green][pre]
TotOUT TotIN
4321 45453[/pre][/green]

---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

TotOUT … TotIN
2132,67 …. 320,40 (=> only table A)

TotOUT … TotIN
170,60 …. 2051,80 (=> only table B)

Expected (correct) figures
TotOUT … TotIN
2307,27 … 2372,20 (=> tables A+B)

Thanks for further UNION-help.

 

[Andrzejek]

Your "Expected (correct) figures" are 'wrong'.
You should get out of your UNION the outcome that looks like this:

[pre]
TotOUT TotIN
2132,67 320,40
170,60 2051,80 [/pre]

Is that what you are getting?


---- Andy

There is a great need for a sarcasm font.

 

[leifoet]

I try to clarify my question : I expect these 2 numbers from the (UNION) SQL query:
1x TotOUT (from table A + B together)
1x TotIN (from table A + B together)

My problem : I am trying to migrate an old FrontPage query code

fp_sQry = "SELECT Sum ([SumOUT]) AS TotOUT, sum ([SumIN]) AS TotIN FROM (SELECT Sum (Out) AS SumOUT, Sum (In) AS SumIN FROM table_B WHERE (member ID = 100)" & _
"UNION ALL SELECT Sum (Out) AS SumOUT, sum (In) AS SumIN FROM table_A WHERE (member ID = 100))"

This still working FrontPage query gives the 2 numbers-result :
TotOUT … TotIN
2307,27 … 2372,20 (=> tables A+B)

However, my 'migrated query' (in my first message of 24/12/2019 about this item) does not work as you see it.
Is it still possible to migrate the old FP query to a working SQL query with an A+B result?

 

[Andrzejek]

You keep saying your (or mine) SQL gives you the 'wrong' results, but you never show what results you are actually getting... :-(

Did you try to run your original "old FrontPage query code"?

[pre]SELECT Sum ([SumOUT]) AS TotOUT, sum ([SumIN]) AS TotIN
FROM
(SELECT Sum (Out) AS SumOUT, Sum (In) AS SumIN
FROM table_B WHERE (member ID = 100)
UNION ALL
SELECT Sum (Out) AS SumOUT, sum (In) AS SumIN
FROM table_A WHERE (member ID = 100))
[/pre]
Because that looks like a valid, correct statement to get what you want.


---- Andy

There is a great need for a sarcasm font.