Solved Union Query with Calculations

[E9-Tech]

I have a Union Query which I want to add calculated field

SELECT TransactionDate, PensionEmployee, PensionEmployer, TransactionType, FundName, Payments, UnitsTransaction, TOT
FROM tblPension
UNION
SELECT PayslipDate, PensionEmployee, PensionEmployer, Null As  Col4, Null As Col5, Null As Col6, Null As Col7, Abs([PensionEmployee]-[PensionEmployer]) AS TOTAL
FROM tblPayslip;

Using the above code when running the query I have the pop up window TOT (beacuase I don't have the column TOT in tblPayslip) that I can click OK

and then it runs the query with the correct result.

In order to avoid the pop up window I changed the code to:

SELECT TransactionDate, PensionEmployee, PensionEmployer, TransactionType, FundName, Payments, UnitsTransaction, Null As TOT
FROM tblPension
UNION
SELECT PayslipDate, PensionEmployee, PensionEmployer, Null As  Col4, Null As Col5, Null As Col6, Null As Col7, Abs([PensionEmployee]-[PensionEmployer]) AS TOTAL
FROM tblPayslip;

As a result I get some weird characters

Changing the code to

SELECT TransactionDate, PensionEmployee, PensionEmployer, TransactionType, FundName, Payments, UnitsTransaction, "TOT"
FROM tblPension
UNION
SELECT PayslipDate, PensionEmployee, PensionEmployer, Null As  Col4, Null As Col5, Null As Col6, Null As Col7, Abs([PensionEmployee]-[PensionEmployer]) AS TOTAL
FROM tblPayslip;

I get the below which again is wrong as it adds TOT to the last 2 records

What should I do?

 

[Gasman]

Just use 0 AS TOT ?

You defined a string as the last field, so what do you expect?

Could even try swapping the Selects over, so as to define the last field correctly?, then Null might well work. Experiment.

 

[E9-Tech]

Just use 0 AS TOT ?

You defined a string as the last field, so what do you expect?

Could even try swapping the Selects over, so as to define the last field correctly?, then Null might well work. Experiment.

Still confused!!!!

I changed the code to

SELECT TransactionDate, PensionEmployee, PensionEmployer, TransactionType, FundName, Payments, UnitsTransaction, ""
FROM tblPension
UNION
SELECT PayslipDate, [PensionEmployee], [PensionEmployer], Null As  Col4, Null As Col5, Null As Col6, Null As Col7, Abs([PensionEmployee]-[PensionEmployer]) As TOT
FROM tblPayslip;

How do I give an different description to the result to Expre1007?

 

[arnelgp]

Something like this:

SELECT TransactionDate, PensionEmployee, PensionEmployer, TransactionType, FundName, Payments, UnitsTransaction, Null As TOT
FROM tblPension
UNION
SELECT PayslipDate, [PensionEmployee], [PensionEmployer], Null As  Col4, Null As Col5, Null As Col6, Null As Col7, Abs([PensionEmployee]-[PensionEmployer]) As TOT
FROM tblPayslip;

 

[Gasman]

Have tried what I suggested in post#2?
Now you could just try Null on it's own

 

[E9-Tech]

Something like this:

SELECT TransactionDate, PensionEmployee, PensionEmployer, TransactionType, FundName, Payments, UnitsTransaction, Null As TOT
FROM tblPension
UNION
SELECT PayslipDate, [PensionEmployee], [PensionEmployer], Null As  Col4, Null As Col5, Null As Col6, Null As Col7, Abs([PensionEmployee]-[PensionEmployer]) As TOT
FROM tblPayslip;

Thanks for the solution.

Something really weird, the email notification I received came with a difference code as per screenshot below, the code in the browser still shows Null As TOT vs the email showing "" As TOT

 

[arnelgp]

what do you mean "email",
on "email" you can either use "" or Null, since the "email" is really a Text.
but for Numeric field, you use 0 or Null (if you really dont' want to show 0).

if you use "", the result is a Text, if for later time you want to Calculate (mathematical calculation) on
this field, then you can't unless you Convert it first to Numeric (like Val() function).

likewise with Null (plus your Union has some calculation on it), you can Further calculate it, like:

try changing TOT to string:

"" As TOT

then calculate it (in immediate window):

?DSum("TOT", "YourQueryName")

then, you will get an error because TOT is String (text).

chang it to:

Null As TOT

and try the dsum, you will get a Numeric answer (no errors).

 

[Gasman]

Thanks for the solution.

Something really weird, the email notification I received came with a difference code as per screenshot below, the code in the browser still shows Null As TOT vs the email showing "" As TOT
View attachment 118486

That just means the code was amended. It was "" when sent. I saw that. Then arnelgp changed it to Null.

 

[E9-Tech]

what do you mean "email",
on "email" you can either use "" or Null, since the "email" is really a Text.
but for Numeric field, you use 0 or Null (if you really dont' want to show 0).

if you use "", the result is a Text, if for later time you want to Calculate (mathematical calculation) on
this field, then you can't unless you Convert it first to Numeric (like Val() function).

likewise with Null (plus your Union has some calculation on it), you can Further calculate it, like:

try changing TOT to string:

"" As TOT

then calculate it (in immediate window):

?DSum("TOT", "YourQueryName")

then, you will get an error because TOT is String (text).

chang it to:

Null As TOT

and try the dsum, you will get a Numeric answer (no errors).

I receive email notifications for any answer I get, the screenshot shows the email I received with "" As TOT, Then if I go to the thread it shows a different code which is strange.

Thanks for the additional information!

 

[arnelgp]

receive email notifications for any answer I get, the screenshot shows the email I received with "" As TOT, Then if I go to the thread it shows a different code which is strange.

maybe it got Edited before you can go to the forum.