Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2009

    Unanswered: Sum of Calculated field in a query

    Hi all,

    I have a query as follows:
    SELECT da_ligne.RFQ_NATURE, sum(da_ligne.Total_Price) as Total FROM (SELECT da_ligne.C_DA, da_ligne.C_REF, da_ligne.RFQ_PRICE, da_ligne.RFQ_CURENC, da_ligne.RFQ_RATE, da_ligne.RFQ_TOOLS, da_ligne.RFQ_NATURE, ((([RFQ_PRICE]*[RFQ_RATE])*[Q_DEM])+([RFQ_TOOLS]*[RFQ_RATE])) AS Total_Price
    Where da_ligne.C_DA="37744" and da_ligne.RFQ_NATURE="MG");
    In the above query, Total_Price is a calculated field.
    I want to get the total of the this calculated field as Total in the same query.

    Any help will be helpful.

    Thanks in advance.

  2. #2
    Join Date
    Apr 2004
    outside the rim
    just glancing quickly at your SQL string, two things stand out:
    1) you don't have a GROUP BY clause, so the query doesn't know how to group the data before calculating the sum
    2) if you want to do the sum on a calculated value - an "Expression" (which you are trying to do) you need to put the expression in the Sum function (not the Expression name) because the Expression name doesn't exist at the time the query calculates - it only exists in the result. Sometimes you can get around this in SQL server, but not in Access.

    One (of several) way around point 2 is to use a select query first that calculates the "totals" expression, then in the subsequent query, group by and sum the expression.
    have fun!

    Todd S.
    click to email

    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET

    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts