Results 1 to 4 of 4

Thread: MS Access Query

  1. #1
    Join Date
    Jun 2009
    Posts
    23

    Unanswered: MS Access Query

    Hi,

    I have a doubt regarding a query:

    SELECT My_table.New_Row*(My_table.New_Row-1) AS NumOfSpecies, Sum(My_table.New_Row)*((Sum(My_table.New_Row))-1) AS AggNew_Row FROM My_table;

    When i run this query, i'm getting following error:
    You tried to execute a query that doesnot include the specified expression
    'My_table.New_Row*(My_table.New_Row-1)' as part of an aggregate function.

    But when i run the query as below i'm not getting any error:
    SELECT My_table.New_Row*(My_table.New_Row-1) AS NumOfSpecies FROM My_table;

    Can you please let me know how can i resolve the issue. Is there any other way to write this query. Please let me know. Thanks

  2. #2
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    I'm not sure what it is exactly that you are trying to achieve, but I'm almost certain that you're trying to do two things in one shot, which cannot be done.

    Your query that works returns a table with one record for each value of "new_row". If you would execute the second part of you original query, you would get a table with one record, a kind of an aggregate value, let's call that S. That's kind of contradictory no?

    If what you are trying to achieve is a table with an entry for each row and in each row the result of the second part (what we called S), then you might will need 1) to execute your query that works and create a table, 2) execute the second to get S and then and 3) an update query that copies S in each record of the table you created in 1).

    Tell us more about your environment (VBA, or just plain Access?), then we can show you code.

  3. #3
    Join Date
    Jun 2009
    Posts
    23
    Yes, this is what i'm trying to acheive as you mentioned as:
    "If what you are trying to achieve is a table with an entry ..."

    I'm using Plain MS Access. I'm able to write it as a separate queries and execute it. But i'm just curious to know if we can do it in a single query. Thanks for your reply.

  4. #4
    Join Date
    Dec 2009
    Location
    Antwerp (Belgium)
    Posts
    33
    I've been wrong before, but I doubt that you can do this in own query.

Posting Permissions

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