Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2004
    Posts
    6

    Unanswered: subqueries cannot be used in the expression

    How do I sum the results of a field on which a calculation was done previously without getting the message "subqueries cannot be used in the expression"

    Eg. myRev: Sum((IIf([Status]="Y",[YourRev],0)))
    allRev: Sum(myRev)

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,444
    Provided Answers: 12
    Calculate myrev in query1, then write query2 that sums this column.
    George
    Home | Blog

  3. #3
    Join Date
    Oct 2004
    Posts
    6
    Thanks George....however is it possible to get this done via one query?

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Do you have a "query budget"?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Oct 2004
    Posts
    6
    I guess it is fair to assume that you dont know the answer startrekker..No budget.....just need to know if it can be achieved by just one query....why use 2 if it is possible to use 1?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - but it is a pain in the pooper. It does not sit terribly well with SQL and set based logic but it can be done. If this is feeding into some sort of report then your report should be able to handle it (and much better and efficiently than SQL).

    If you REALLY want the sql we can do that too

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by wilrich
    why use 2 if it is possible to use 1?
    Code reuse!

  8. #8
    Join Date
    Oct 2004
    Posts
    6
    Thanks Pootle Flump...Painful it definitely is....have tried a few times to incorporate in SQL without success....would appreciate any example re SQL implementation

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    SELECT    groupingcol
            , Sum((IIf([Status]="Y",[YourRev],0))) AS myRev
            , 0 AS ordercol
    FROM    mytable
    UNION ALL
    SELECT    'All' AS groupingcol
            , Sum((IIf([Status]="Y",[YourRev],0))) AS myRev
            , 1 AS ordercol
    FROM    mytable
    ORDER BY ordercol
    ANSI SQL and other implementations have better methods for this than JET SQL. I would not recomend doing this in one query generally - if the expression you used was subject to change then by doing it like this you need to change *two* expressions to account for the change. I would prefer to do this in a report or, failing that, two queries with the expression written once in the referenced query.

    HTH

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by wilrich
    I guess it is fair to assume that you dont know the answer startrekker..No budget...
    Simply put, I just know how much easier it is to use two in cases like this... or just get the report to do the totals.

    Quote Originally Posted by wilrich
    ...why use 2 if it is possible to use 1?
    Simplicity, easier maintenance and re-useability.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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