One query works fine, the other not.....
in [Help]
|
From: The Frog on 19 Jul 2010 10:45 Hi Everyone, I have a seemingly strange situation where one query runs perfectly, and a copy of the query with an altered 'where' type condition causes the query to fail. In short they are aggregate queries running a type of conditional sum. The only thing that is altered between them is the evaluation condition (or at least as far as I can tell). The second query comes back to me with some error about not using the expression in a group by, and then (just to humour access) I tried the group by approach and I am told that you cant group by an aggregate function (which is what you would expect). They are both based of exactly the same data (a query), and both conditions are text evaluations, just on different fields of the underlying data. Here is the one that works: SELECT qryREPORT_BASE_SOLL.channel AS VS, "DROGERIE" AS CHANNEL, IIf(qryREPORT_BASE_SOLL.channel Like ("*Drog*"),Sum([SOLL]),Null) AS SHOULD FROM qryREPORT_BASE_SOLL WHERE (((qryREPORT_BASE_SOLL.segment)="Cat Pouch")) GROUP BY qryREPORT_BASE_SOLL.channel ORDER BY qryREPORT_BASE_SOLL.channel; and here is the one that doesnt: SELECT qryREPORT_BASE_SOLL.channel AS VS, "SCHLECKER" AS CHANNEL, IIf(qryREPORT_BASE_SOLL.retailer Like("*Schlecker*"),Sum([SOLL]),Null) AS SHOULD FROM qryREPORT_BASE_SOLL GROUP BY qryREPORT_BASE_SOLL.channel ORDER BY qryREPORT_BASE_SOLL.channel; The exact error message is: You tried to execute a query that does not include the specified expression 'IIf(qryREPORT_BASE_SOLL.retailer Like "*Schlecker*",Sum([SOLL]),Null' as part of an aggregate function. Can anyone tell me why the first works and the second does not? All the columns exist in the underlying data (provided by a query), and the values for the evaluation are valid. I am stumped. Cheers The Frog
From: Salad on 19 Jul 2010 11:49 The Frog wrote: > Hi Everyone, > > I have a seemingly strange situation where one query runs perfectly, > and a copy of the query with an altered 'where' type condition causes > the query to fail. In short they are aggregate queries running a type > of conditional sum. The only thing that is altered between them is the > evaluation condition (or at least as far as I can tell). The second > query comes back to me with some error about not using the expression > in a group by, and then (just to humour access) I tried the group by > approach and I am told that you cant group by an aggregate function > (which is what you would expect). They are both based of exactly the > same data (a query), and both conditions are text evaluations, just on > different fields of the underlying data. > > Here is the one that works: > > SELECT qryREPORT_BASE_SOLL.channel AS VS, "DROGERIE" AS CHANNEL, > IIf(qryREPORT_BASE_SOLL.channel Like ("*Drog*"),Sum([SOLL]),Null) AS > SHOULD > FROM qryREPORT_BASE_SOLL > WHERE (((qryREPORT_BASE_SOLL.segment)="Cat Pouch")) > GROUP BY qryREPORT_BASE_SOLL.channel > ORDER BY qryREPORT_BASE_SOLL.channel; > > and here is the one that doesnt: > > SELECT qryREPORT_BASE_SOLL.channel AS VS, "SCHLECKER" AS CHANNEL, > IIf(qryREPORT_BASE_SOLL.retailer Like("*Schlecker*"),Sum([SOLL]),Null) > AS SHOULD > FROM qryREPORT_BASE_SOLL > GROUP BY qryREPORT_BASE_SOLL.channel > ORDER BY qryREPORT_BASE_SOLL.channel; > > The exact error message is: > You tried to execute a query that does not include the specified > expression 'IIf(qryREPORT_BASE_SOLL.retailer Like > "*Schlecker*",Sum([SOLL]),Null' as part of an aggregate function. > > Can anyone tell me why the first works and the second does not? All > the columns exist in the underlying data (provided by a query), and > the values for the evaluation are valid. I am stumped. > > Cheers > > The Frog Couldn't tell you why. Do the queries work fine if you remove the IIF...Should line? What happens if you put the IIF...Should line in the GroupBy line as well?
From: Bob Barrows on 19 Jul 2010 12:00 The Frog wrote: > Hi Everyone, > > I have a seemingly strange situation where one query runs perfectly, > and a copy of the query with an altered 'where' type condition causes > the query to fail. In short they are aggregate queries running a type > of conditional sum. The only thing that is altered between them is the > evaluation condition (or at least as far as I can tell). The second > query comes back to me with some error about not using the expression > in a group by, and then (just to humour access) I tried the group by > approach and I am told that you cant group by an aggregate function > (which is what you would expect). They are both based of exactly the > same data (a query), and both conditions are text evaluations, just on > different fields of the underlying data. > > Here is the one that works: > > SELECT qryREPORT_BASE_SOLL.channel AS VS, "DROGERIE" AS CHANNEL, > IIf(qryREPORT_BASE_SOLL.channel Like ("*Drog*"),Sum([SOLL]),Null) AS Why did you put the parentheses around "*Drog*" ? > SHOULD > FROM qryREPORT_BASE_SOLL > WHERE (((qryREPORT_BASE_SOLL.segment)="Cat Pouch")) > GROUP BY qryREPORT_BASE_SOLL.channel > ORDER BY qryREPORT_BASE_SOLL.channel; > > and here is the one that doesnt: > > SELECT qryREPORT_BASE_SOLL.channel AS VS, "SCHLECKER" AS CHANNEL, > IIf(qryREPORT_BASE_SOLL.retailer Like("*Schlecker*"),Sum([SOLL]),Null) > AS SHOULD > FROM qryREPORT_BASE_SOLL > GROUP BY qryREPORT_BASE_SOLL.channel > ORDER BY qryREPORT_BASE_SOLL.channel; > > The exact error message is: > You tried to execute a query that does not include the specified > expression 'IIf(qryREPORT_BASE_SOLL.retailer Like > "*Schlecker*",Sum([SOLL]),Null' as part of an aggregate function. > > Can anyone tell me why the first works and the second does not? All > the columns exist in the underlying data (provided by a query), and > the values for the evaluation are valid. I am stumped. > Since retailer is not being grouped by, the query cannot evaluate a non-aggregated retailer. I would change it to: SUM(IIf(qryREPORT_BASE_SOLL.retailer Like("*Schlecker*"),[SOLL])) AS SHOULD -- HTH, Bob Barrows
From: The Frog on 19 Jul 2010 12:59 Bob, if I use the following as you suggest: > SUM(IIf(qryREPORT_BASE_SOLL.retailer Like("*Schlecker*"),[SOLL])) AS > SHOULD wouldn't that mean the VS part of the query would only also come back with values where the condition is true? This would in effect change the reported results by leaving out rows which would have a null value for the aggregation wouldn't it? I will give it a whirl tomorrow morning too and report back the results. Cheers The Frog
From: Bob Barrows on 19 Jul 2010 13:14 The Frog wrote: > Bob, if I use the following as you suggest: >> SUM(IIf(qryREPORT_BASE_SOLL.retailer Like("*Schlecker*"),[SOLL])) AS >> SHOULD > wouldn't that mean the VS part of the query would only also come back > with values where the condition is true? This would in effect change > the reported results by leaving out rows which would have a null value > for the aggregation wouldn't it? I will give it a whirl tomorrow > morning too and report back the results. > No, why would it? it's not in the WHERE clause. It's only controlling how the calculation is performed. To see for yourself, Change the query to: SELECT qryREPORT_BASE_SOLL.channel AS VS, "SCHLECKER" AS CHANNEL, retailer , SOLL,IIf(retailer Like "Schlecker",[SOLL],) AS ValueToBeSummed FROM qryREPORT_BASE_SOLL When you run it, you will see all the rows are returned: you will just see NULLS in the rows where retailer contains "Schlecker" - and you should see that retailer needs to be evaluated on a row-by-row basis. The equivalent would look like this: SELECT VS, CHANNEL, Sum(ValueToBeSummed) AS SHOULD FROM ( SELECT qryREPORT_BASE_SOLL.channel AS VS, "SCHLECKER" AS CHANNEL, ,IIf(retailer Like "Schlecker",[SOLL],) AS ValueToBeSummed FROM qryREPORT_BASE_SOLL ) AS q GROUP BY VS ORDER BY VS .... but of course you don't have to do it this way: I just added it for illustration. -- HTH, Bob Barrows
|
Next
|
Last
Pages: 1 2 3 Prev: Convering from Access97 to Access2010 Next: Using VBA to modify field in current record |