"Query to complex"

[Epona]

I am a bit stumped - please help.



I need to calculate the stock that must be purchased per month over a 12 month period.

The mathematical formulae is;
for M2
IF [M1]>0,[M1]+[M2],[M2]

M3 would then be
IF [M2]>0,[M2]+[M3],[M4]

Then rolling forward till month 12

The data is organised as a table with each month being a field. My problem is that if I use the IIF stateemnt in the builder when I get to month 10 i get the error "Query to complex".

Does anyone know if this can be done with a SQL query and if so what would the query be? Or if there is another way of doing this.

There will be subsequent calcualtions on the fields based on lead times so I need to retain the structure as is if possible.

[Robeen]

I'm not sure if you've considered this already, but you could create a function [VBA] that does all your logic.
You can pass your function the Month [M2, M3 . . .] and have it return the relevant data to you.
I'm not sure I understand exactly what you need to do but . . .
Your function might look something like this:

 
Function MonthInfo(MonthName as String) As Double
 
Dim MthNm as String
 
MthNm = MonthName
 
Select Case MthNm 
     Case "M1" 
          'Do something here . . .
     Case "M2"
          'Do something here . . .
End Select
 
End Function

In the Query, your field will look something like this:
FieldName: MonthInfo(MonthName)

I'm not sure I have totally understood your scenario - but you might be able to take what I've given you and run with it.

Let me know if you have questions.

[Epona]

Thanks, that looks promising - let me see if I can get it to work

[Robeen]

I made a change to my post:
FieldName: MonthInfo(MonthName)

Let us know how it goes!! All the best.