Results 1 to 4 of 4

Thread: [RESOLVED] Typical Calculations ...

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    Resolved [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.

  2. #2

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    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

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Dec 2014
    Posts
    326

    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
  •  



Click Here to Expand Forum to Full Width