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:
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
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.