A VERY complicated query... 1

[FederalProgrammer]

All right, let's state what I need first:
Consider a number X; here's how we get it:
Select Sum(X)
From tblBlah inner join millionOtherTables
Group BY tblBlah.someField

The Query goes through the following logic:
If contion1 is true do the following:
X/aTable.Field if contion2 is true;
X/anotherTable.Field if condition2 is false and anotherTable.Field <> 0;
If condition1 is false, simply return X.

I've got all the (IIFs) figured out, and this is not the problem: here's what i do:

SUM(iif(Cond1,
iif(cond2, x/aTable.Field, iif(anotherTable.field = 0, 0, x/anotherTable.field)), X)).

Easy enought.. right? WRONG!!
You see the divisor, aTable.Field; Well, I don't need its value, as is; I need to divide X by the total of aTable.Field for that particular group.
I've tried the following:
x/Sum(aTable.Field). However access states that I cannot have aggregate functions inside iif! I have tried moving IIFs outside of SUM; The first IIF goes through; However the 2nd one complains about the fact that "iif(Cond2" is not part of the "Group By" or whatever;
Anyways, if you've understood the problem, please drop me a line. it means a lot to have someone on the same page as me!

cheers!

 

[MichaelRed]

aTable.Field is ~ consttant (at least for a given instance)? Calculate it seperatly. Make the 'existing' query a parameter query, obtaining the parameter (aTable.Field1) the parameter.

For the 'set' (anotherTable.Field), do each of the calcs as above, and place in a temp rs (with anotherTable.Field and the aggreate value) . Join the Temp rs to the currrent atable and use the join to ferret out the necessary divisor.





MichaelRed
mlred@verizon.net

 

[JonFer]

You could create summary queries of aTable and anotherTable and use those in your main query instead of joining to the tables directly. That would simplify your calculations.

You can also try to expand something like this. I am assuming there is a 1-to-many relationship between the table with x and the table with field1 and field2:

Max(x)/Sum(IIf(Cond1,field1,field2))

The above won't allow you to set the expression to 0 but you can try it out and see if you get closer.

John

http://www.interautomateconsulting.com

 

[FederalProgrammer]

I really apreciate your suggestion. I'm gonna try it; I wish there was a way to do this in a single query though..

cheers!

 

[FederalProgrammer]

Max(x)/Sum(IIf(Cond1,field1,field2))"

I'm not so sure... Max(X)? I guess I got to think about this a bit more... I require Sum(X).
I'll try it anyways.. and thanx so much for the reply... what do you know... there are 2 other people who understand what i'm saying!!

cheers!