Prev: Convering from Access97 to Access2010
Next: Using VBA to modify field in current record
From: The Frog on 20 Jul 2010 03:16
Hi Bob,

Spot on. Changing the order of logical evaluation and operation to
what you suggested Sum(IIf(..... worked like a charm. I am thankful
for the input. Still cant figure for the life of me why it works for
one and not the other though. All rows come back, and now that I have
had time to think about it I understand why - I was just a little
tired yesterday to take it all in I suppose.

Bannana, thankyou for your suggestions too. If I ever find out what is
behind the apparent discrepancy in execution between the two seemingly
(at least logically) identical queries, I will be sure to drop a note
here in the group.

Cheers and thanks

The Frog
From: Bob Barrows on 20 Jul 2010 08:22
The Frog wrote:
> Hi Bob,
>
> Spot on. Changing the order of logical evaluation and operation to
> what you suggested Sum(IIf(..... worked like a charm. I am thankful
> for the input. Still cant figure for the life of me why it works for
> one and not the other though.

I'm sorry, I thought it would be obvious:
In the version that works, you are evaluating a grouped by column (channel)
in the formula, so the query engine has no problem with it appearing in the
SELECT clause without aggregation. The problem came up when you changed to a
non-grouped-by column.

PS. Are you sure the original query gave you the right answer? Now that I
think about it a little, I think there's a possibility that despite it
"working" (running without error) it was actually giving you the wrong
answer. Change the logic to the the new alternative and see if it gives you
a different answer.


From: David W. Fenton on 20 Jul 2010 12:51
"Bob Barrows" <reb01501(a)yahoo.com> wrote in
news:9yg1o.24093$o27.9024(a)newsfe08.iad:

> The Frog wrote:
>> Hi Bob,
>>
>> Spot on. Changing the order of logical evaluation and operation
>> to what you suggested Sum(IIf(..... worked like a charm. I am
>> thankful for the input. Still cant figure for the life of me why
>> it works for one and not the other though.
>
> I'm sorry, I thought it would be obvious:
> In the version that works, you are evaluating a grouped by column
> (channel) in the formula, so the query engine has no problem with
> it appearing in the SELECT clause without aggregation. The problem
> came up when you changed to a non-grouped-by column.
>
> PS. Are you sure the original query gave you the right answer? Now
> that I think about it a little, I think there's a possibility that
> despite it "working" (running without error) it was actually
> giving you the wrong answer. Change the logic to the the new
> alternative and see if it gives you a different answer.

I find myself really missing StackOverflow's capability for voting
up answers, as this is one where I really wanted to click the +1
button -- it's in my opinion a really fine answer.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: Bob Barrows on 20 Jul 2010 13:12
David W. Fenton wrote:
> "Bob Barrows" <reb01501(a)yahoo.com> wrote in
> news:9yg1o.24093$o27.9024(a)newsfe08.iad:
>
>> The Frog wrote:
>>> Hi Bob,
>>>
>>> Spot on. Changing the order of logical evaluation and operation
>>> to what you suggested Sum(IIf(..... worked like a charm. I am
>>> thankful for the input. Still cant figure for the life of me why
>>> it works for one and not the other though.
>>
>> I'm sorry, I thought it would be obvious:
>> In the version that works, you are evaluating a grouped by column
>> (channel) in the formula, so the query engine has no problem with
>> it appearing in the SELECT clause without aggregation. The problem
>> came up when you changed to a non-grouped-by column.
>>
>> PS. Are you sure the original query gave you the right answer? Now
>> that I think about it a little, I think there's a possibility that
>> despite it "working" (running without error) it was actually
>> giving you the wrong answer. Change the logic to the the new
>> alternative and see if it gives you a different answer.
>
> I find myself really missing StackOverflow's capability for voting
> up answers, as this is one where I really wanted to click the +1
> button -- it's in my opinion a really fine answer.

Well! Thanks! :-)
And there I was feeling a little embarassed about not seeing that
possible problem earlier!

--
HTH,
Bob Barrows


From: The Frog on 21 Jul 2010 03:11
Hi again,

Thanks for the clarification, it makes perfect sense. I also took your
advice and altered the other queries to the same Sum(IIF( logic. The
answers on this dataset are the same, but it is better to be sure for
the future as the dataset grows. I really appreciate your help with
this. My understanding has grown too - always an added bonus :-)

Cheers and Thanks

The Frog
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Convering from Access97 to Access2010
Next: Using VBA to modify field in current record