Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2007
    Posts
    5

    Unanswered: Aggregate expression error in query

    Hullo, I'm trying to add a field to an already-working query, just to show the human-readable route name (the wonderfully chosen Route.Route, how fun it is to inherit a database). Here's the expression I'm working on:

    Code:
    SELECT Sum(Customers.PaperBagsFrequency)*Forms!frmOrderTotals!Weeks AS [Paper bags], Sum(Customers.CanPlasticBagsFrequency)*Forms!frmOrderTotals!Weeks AS [Can bags], Sum(Customers.CardBagsFrequency)*Forms!frmOrderTotals!Weeks AS [Card bags], Route.Route AS [Route name]
    FROM Route INNER JOIN Customers ON Route.[Route ID] = Customers.Route
    WHERE (((Customers.Route)=[Forms]![frmOrderTotals]![Route]));
    When I run it I get the following error:

    You tried to execute a query that does
    not include the specified expression 'Route name' as part of an aggregate function.
    I've spent some time searching forums and found that I should include the Route.Route field in a GROUP BY clause, which I've tried. I've added "GROUP BY [Route name]" and I've tried inserting a meaningless clause to the WHERE part - "AND [Route name] LIKE '%'". I still get the same error.

    Can anybody help? I just need a query that will include the route name alongside the calculated figures for a report.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tomchance
    I've added "GROUP BY [Route name]"
    Almost.
    Code:
     
    GROUP BY Route.Route
    Have a go through this - it will only take about an hour:
    http://www.w3schools.com/sql/default.asp
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2007
    Posts
    5
    *Slaps forehead*

    Thanks!

Posting Permissions

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