-
Dec 5th, 2017, 03:16 PM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Typical Calculations ...
Hi,
Using the following code in another form which is fine. But now I need to use it for another form with some calculations as below:
Need to add an extra dummy column to calculate to values of A20, A22 & A24 as PurchaseInvoiceTotal (Taxable Value (A20) + CGST Value (A22) + SGST Value (A24) AS PurchaseInvoiceTotal)
Then
PurchaseInvoiceTotal to be rounded up and A26 field to be removed in modified code.
Code:
"Select A3,A2,A4,A5,A6,A1,SUM(A15),SUM(A20),A21,SUM(A22),A23,SUM(A24),SUM(A26),-Int(-SUM(A26)+.5) AS AmountRounded from NewPurchasesDBTable GROUP BY A3,A2,A4,A5,A6,A1,A21,A23 ORDER BY A1 ASC"
Thanks for any support extended.
-
Dec 5th, 2017, 05:30 PM
#2
Thread Starter
Hyperactive Member
Re: Typical Calculations ...
I tried the following code but getting the error : Subqueries cannot be used in the expression (SUM(++)).
"Select A3,A2,A4,A5,A6,A1,SUM(A15),SUM(A20),A21,SUM(A22),A23,SUM(A24),SUM(A20)+SUM(A22)+SUM(A24) AS TotalInoviceValue,-Int(-SUM(TotalInoviceValue)+.5) AS AmountRounded from NewPurchasesDBTable GROUP BY A3,A2,A4,A5,A6,A1,A21,A23 ORDER BY A1 ASC"
The RED part in the code is throwing that error.
-
Dec 5th, 2017, 07:53 PM
#3
Re: Typical Calculations ...
I think that you would have to repeat the calculation rather than use the aliased column, i.e.
Code:
SUM(A20)+SUM(A22)+SUM(A24) AS TotalInoviceValue,-Int(-SUM(A20)-SUM(A22)-SUM(A24)+.5) AS AmountRounded
-
Dec 7th, 2017, 05:04 AM
#4
Thread Starter
Hyperactive Member
Re: Typical Calculations ...
Thanks a lot for your kind support. Problem resolved.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|