Querry is too complex...solving

[moddien]

Hey guys,

i am currently working on a access database where I get on my calculations always this "too complex" message! The Problem why i get this is that the querries access on another querry, which is a little bit too complicated (I think)! I have a Start and a Enddate and between a lot of periods, which could be monthly, quarterly, semi-annual or annual! besed on this, I want to know, which period is currently (date entered in a form) is activ...i tried this with (and also "works"):
CurrentPeriod: IIf([period2diff]<1,[period1diff],IIf([period3diff]<1,[period2diff],IIf([period4diff]<1,[period3diff],IIf([period5diff]<1...

This is based on another querry which has the required differences between the startdate and the current that. It could be that there are 30 periods per primary-key.. so it gets for access a little bit to much...the calculation behind need "hours" to complete! I am currently not very skilled in vba...i can interpret and adapt a bit and thats it...maybe there is someone who has exact calc. done before or could solve this prob in a few minutes! many thanks in advance...

Greets, Martin

 

[doco]

You indicate by elipse (...) the the nested IIF continue. The immediate IIF has a limit to the number of nests you can have. I do not remember offhand what that is but I believe 8 is the magic number.

 

[moddien]

they are some more...currentPeriod: IIf([period1diff]<1,[period1diff],IIf([period2diff]<1,[period2diff],IIf([period3diff]<1,[period3diff],IIf([period4diff]<1,[period4diff],IIf([period4diff]<1,[period4diff],IIf([period5diff]<1,[period5diff],IIf([period6diff]<1,[period6diff],IIf([period7diff]<1,[period7diff],IIf([period8diff]<1,[period8diff],IIf([period9diff]<1,[period9diff],IIf([period10diff]<1,[period10diff],IIf([period11diff]<1,[period11diff],IIf([period12diff]<1,[period12diff],IIf([period13diff]<1,[period13diff],"x"))))))))))))))

but...i want to get rid of these querry and maybe that one with the differencies calc...maybe a function would solve that????

 

[doco]

I believe this is your problem - too many nested IIF. There are several approaches to this issue. One is a function and using SELECT CASE; SQL CASE WHEN ELSE, etc.

 

[doco]

What is periodXdiff and where does it come from? Are these field names?

 

[moddien]

And exactly there is my problem...what I have to put in there! As i said I am not very familiar with vba..so I have currently the problem how to get the right syntax for it...Unfortunately it would take me days to get familiar with vba. But may I have to do this :-(

 

[doco]

Need more information such as are the period[x]diff field names? or textbox names? What is CurrentPeriod? Is it a variable; are you attemting to update a record in another table? Is it a textbox on a form?

 

[doco]

Unfortunately it would take me days to get familiar with vba

If you are going to work with Access you WILL need to 'get familiar' - its a requirement. And it will take years

 

[moddien]

Thank you very much for your help in advance...you will to me a great favour...i attached the mdb to write no novel ;-)...
there you can see 4 querries mainly based on the "data" table...1st step is in querry "_Period Starts calc + Check Bankhols" .(there he checked also the bankhols and weekends" ..all periods will be displayed ...based on that he will calc the differences in "period starts differences calc"...and than he gives me at least in "period start & Maturity" the desired start and and date.

All calculation are based on the curent calc date in the textbox in form " starting screen...before it isnt opened he will to nothing...

 

[moddien]

hm..you mean years??? I have 2 month...must unfortunately be enough :-( ... But they are all saying...that vba isnt so difficult...I already recognized by myself that this is a true lie ;-)

 

[DCrake]

Simple Software Solutions

What are actuallly trying to achieve

If you can't write it down it can't be done

I am sure that this could be greatly simplified if the answer to the question was apparant.

CodeMaster:

 

[moddien]

what I need is based on the data table a new table or querry which gives me all period start dates for the duration (they are calculated based on the compounded periods the Start and endate in the "data" table)
They (period starts and ends) have to be checked (as in "_Period Starts calc + Check Bankhols" already done with the function bankhols ) if they are on a weekend or bankhol....
2nd step is to find out which is the current running period...so that I get a "current" period start and enddate based on the entered date in the form (textbox)
So the ultimate goal would be to have two tables/querries ...one with all periods between the start and enddates and one with the current start and enddate..

 

[moddien]

I forgot to note that their must be the possibility to correct the calculated periods...thats why in table "data" are also periods listed...if they are is entered a date it has to use this period for the current_period.......gets to complex??????? sorry for that

 

[Guus2005]

It not only is complex it is also extremely slow.
I believe you have to make a helptable for all the possibilities in your iif statements and then connect to it.

By the looks of it, it might be helpfull if you post a sample database.

 

[moddien]

is already done in post 9

 

[moddien]

By the looks of it, it might be helpfull if you post a sample database.

is already done in post 9

 

[doco]

Some major normalisation issues. I cannot figure out what you are trying to do.

Also, I tried to run the query with all the IIf statements and had to eventually Ctl + Alt + Del to get out of some wierd fatal embrace.

 

[Mike375]

I just clicked through the query (I did not enter the parameter) and got..to complex or is written wrong.

There are a couple of things you could try when query is too complex.

With IIF break it down to two or more fields. That is,the second calculated field uses the result of IIF from the first field and the third field uses the results from the second field in IIF.

You can also reduce what you are doing in the query and change the "reduced query" to a Make Table query. You then do the rest of the query against the table that resulted from the Make Table query. This is the same principle as a relay race where the baton changes hands every 400 yards or whatever. If you do it this way then you need a macro or code to run the query that does the Make Table so as it updates.