To Complex of a query

[ED]

I am attempting to to write a query that has a numerous nested IIf
statements. The problem that I am having is that it is to long of a
query to be built in design mode and when I build it in sql mode after
a certain point it give me the error message that the expression is to
complex. Below is the sql code that I am using (this works so far,
anything added to the code will give me the to complex error message.)

SELECT [Work Order by Activity_Quantity].WONUM, [Total Hours per Work
Order].[Total Hrs per WO], Sum((IIf([Total Hours per Work
Order]![Month]=1,IIf([Work Order by Activity_Quantity]![Activity
Quantity]>1,(1*[Activity Type Index Static Table]![Jan Index])+([Work
Order by Activity_Quantity]![Activity Quantity]-1)*[Activity Type
Index Static Table]![Jan Index]*[Activity Type Index Static
Table]![Secondary Mutiplier],[Activity Type Index Static Table]![Jan
Index]),(IIf([Total Hours per Work Order]![Month]=2,IIf([Work Order by
Activity_Quantity]![Activity Quantity]>1,(1*[Activity Type Index
Static Table]![Feb Index])+([Work Order by
Activity_Quantity]![Activity Quantity]-1)*[Activity Type Index Static
Table]![Feb Index]*[Activity Type Index Static Table]![Secondary
Mutiplier],[Activity Type Index Static Table]![Feb Index]),(IIf([Total
Hours per Work Order]![Month]=3,IIf([Work Order by
Activity_Quantity]![Activity Quantity]>1,(1*[Activity Type Index
Static Table]![Mar Index])+([Work Order by
Activity_Quantity]![Activity Quantity]-1)*[Activity Type Index Static
Table]![Mar Index]*[Activity Type Index Static Table]![Secondary
Mutiplier],[Activity Type Index Static Table]![Mar Index]),(IIf([Total
Hours per Work Order]![Month]=4,IIf([Work Order by
Activity_Quantity]![Activity Quantity]>1,(1*[Activity Type Index
Static Table]![Apr Index])+([Work Order by
Activity_Quantity]![Activity Quantity]-1)*[Activity Type Index Static
Table]![Apr Index]*[Activity Type Index Static Table]![Secondary
Mutiplier],[Activity Type Index Static Table]![Apr Index]),(IIf([Total
Hours per Work Order]![Month]=5,IIf([Work Order by
Activity_Quantity]![Activity Quantity]>1,(1*[Activity Type Index
Static Table]![May Index])+([Work Order by
Activity_Quantity]![Activity Quantity]-1)*[Activity Type Index Static
Table]![May Index]*[Activity Type Index Static Table]![Secondary
Mutiplier],[Activity Type Index Static Table]![May Index]),(IIf([Total
Hours per Work Order]![Month]=6,IIf([Work Order by
Activity_Quantity]![Activity Quantity]>1,(1*[Activity Type Index
Static Table]![June Index])+([Work Order by
Activity_Quantity]![Activity Quantity]-1)*[Activity Type Index Static
Table]![June Index]*[Activity Type Index Static Table]![Secondary
Mutiplier],[Activity Type Index Static Table]![June
Index]),(IIf([Total Hours per Work Order]![Month]=7,IIf([Work Order by
Activity_Quantity]![Activity Quantity]>1,(1*[Activity Type Index
Static Table]![July Index])+([Work Order by
Activity_Quantity]![Activity Quantity]-1)*[Activity Type Index Static
Table]![July Index]*[Activity Type Index Static Table]![Secondary
Mutiplier],[Activity Type Index Static Table]![July
Index]),0))))))))))))))) AS [Total Index Points]
FROM [Total Hours per Work Order] INNER JOIN ([Work Order by
Activity_Quantity] INNER JOIN [Activity Type Index Static Table] ON
[Work Order by Activity_Quantity].Activity = [Activity Type Index
Static Table].[Activity Number]) ON [Total Hours per Work Order].WONUM
= [Work Order by Activity_Quantity].WONUM
GROUP BY [Work Order by Activity_Quantity].WONUM, [Total Hours per
Work Order].[Total Hrs per WO];

I am attempting to find what month work order and activity are from
and then grab that month's index number and calculate the amount of
index points (from a static table) a given order should have per
month. The equation is taking the static table index number and
multiplying it by the total hours per activity per work order (from a
query). I was wondering if there was a way to complete this in VB.

Any help will be appreciated!
Thanks

[John Winterbottom]

"ED" <da******@hotmail.com> wrote in message
news:ad**************************@posting.google.c om...

I am attempting to to write a query that has a numerous nested IIf
statements. The problem that I am having is that it is to long of a
query to be built in design mode and when I build it in sql mode after
a certain point it give me the error message that the expression is to
complex. Below is the sql code that I am using (this works so far,
anything added to the code will give me the to complex error message.)

I am attempting to find what month work order and activity are from
and then grab that month's index number and calculate the amount of
index points (from a static table) a given order should have per
month. The equation is taking the static table index number and
multiplying it by the total hours per activity per work order (from a
query). I was wondering if there was a way to complete this in VB.

Use the switch() function instead of IIf()

[Ernie DAmato]

I understand how to use the switch to determine what month that it is
but I will still have to use the IIF in order to calculate how many
index points are needed. After I determine the month I need to
determine the quantity and if it is greater than one. If it is greater
than one I need to multiply the first quantity by the index and then add
the remaining quantity to a mutiplier and then to the index. I dont
know how I will be able to complete this in one query or if I need to
try to break up the query into further steps.

The equation is as follows:

if month = 1, then
if quantity > 1, then
1*jan index + (quantity - 1) * (jan index*multiplier)
else jan index
if month = 2 repeat for all months

month is being brought in by a query, but it is stored in table

quantity is being brought in from a table

index and multiplier brought in from a static table

I hope that this explains what I am trying to do better.

Thanks

*** Sent via Devdex http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!

[John Winterbottom]

"Ernie DAmato" <da******@hotmail.com> wrote in message
news:40**********************@news.newsgroups.ws.. .

I understand how to use the switch to determine what month that it is
but I will still have to use the IIF in order to calculate how many
index points are needed. After I determine the month I need to
determine the quantity and if it is greater than one. If it is greater
than one I need to multiply the first quantity by the index and then add
the remaining quantity to a mutiplier and then to the index. I dont
know how I will be able to complete this in one query or if I need to
try to break up the query into further steps.

The equation is as follows:

if month = 1, then
if quantity > 1, then
1*jan index + (quantity - 1) * (jan index*multiplier)
else jan index
if month = 2 repeat for all months

month is being brought in by a query, but it is stored in table

quantity is being brought in from a table

index and multiplier brought in from a static table

I hope that this explains what I am trying to do better.

sql server's case statement is really what you need here - a shame that Jet
never introduced it. Oh well. One suggestion I have is to make a derived
table to produce the indexes, so the query would look something like this
select IIf(q.quantity>1, 1* A.idx + (q.quantity - 1) * (A.idx*m.multiplier),
A.idx)
from
(
select <keycolumn>,
Switch
(
[month] = 1, jan_index,
[month] = 2, feb_index
.....
[month] = 12, dec_index
) as idx
) as A

inner join <rest of tables go here>

I'm not sure if you follow this logic. But, I also suspect there is a much
better, simpler way to do this. Twelve case statements, one for each month
sounds like a bad design or bad query logic or both. Post your table
structures with some sample data if you need more help.

[ED]

I understand how to use the switch to determine what month that it is
but I will still have to use the IIF in order to calculate how many
index points are needed. After I determine the month I need to
determine the quantity and if it is greater than one. If it is greater
than one I need to multiply the first quantity by the index and then add
the remaining quantity to a mutiplier and then to the index. I dont
know how I will be able to complete this in one query or if I need to
try to break up the query into further steps.

The equation is as follows:

if month = 1, then
if quantity > 1, then
1*jan index + (quantity - 1) * (jan index*multiplier)
else jan index
if month = 2 repeat for all months

month is being brought in by a query, but it is stored in table

quantity is being brought in from a table

index and multiplier brought in from a static table

I hope that this explains what I am trying to do better.

Thanks