If statement in an query

[tsvetkovd]

I did the following statement : Inspection_update: IIf([ConditionCode]>3,[FrequencyInMonths]-12,[FrequencyInMonths]+12) in a query. It works well. I wonder how can I do some changes and write another statement in that query to make the values in Inspection_update not to be less than 12 and more than 36 and at the same time keep the above iif statement. Is that possible, if not does someone knows how can I do that? Please help me get it done. Thank you

 

[CJ_London]

I'm trying to understand what you require - you can write as many 'statements' in a query as you like (subject to a maximum of 255)

can you give some examples of what you have as values and what you want them to be

 

[tsvetkovd]

Yes, sorry for not specifying. The values are the frequency of inspections in months. They are between 12 and 36 months in column [FrequencyInMonths]. To update the inspection frequency I wrote the above mentioned iif statement(which increases the frequency if the [ConditionCode] shows that the somponents are in good condition and decreases it if the condition is poor). The problem is that there is an allowable minimum of frequency of 12 months and maximum of 36 months. And when I run the query some values go below the minimum and some above the maximum. How can I fix that? I can't figure out what the statement should look like in that case and how to close the statement (with ; or some other indication) and start a new one? Thanks for your help

 

[CJ_London]

Does this do what you want?

IIf([ConditionCode]>3,iif([FrequencyInMonths]<24,12,[FrequencyInMonths]-12),iif([FrequencyInMonths]>24,36,[FrequencyInMonths]+12))

 

[tsvetkovd]

You are great . Thanks a milion. I wish a could be able to help you about anything. Thank you! Have a good day

 

[CJ_London]

Happy to help

 

[tsvetkovd]

Hi again, I am afraid I need your help again. This time the tings are get a lot more complicated(at least for me) . The situation is as follows:
*From time to time, the organization decided to change the inspection frequency for a list
of component types on bridges of selected construction types. These inspection policy
changes are specified as in increase or decrease in the inspection interval (in months),
subject to an allowable minimum and maximum interval for each combination of
component type and construction type. For example, it might be decided to extend the
inspection interval for the cables on all cable-stayed bridges by adding 6 months to the
current interval per bridge, subject to a minimum of 12 months and a maximum of 36.
Develop and test an SQL procedure to apply an inspection policy update to all
affected bridge components. This should apply all updates for a particular review
date to all affected bridge components in a single run of the procedure. If the
minimum or maximum limits for a particular component would be breached by
an update, the update should not be rejected, but the inspection interval for the
component should be set equal to the minimum or maximum, as appropriate.
You should include some simple mechanism to prevent the possibility of
accidentally applying a change twice in succession. How can I do that in an update query? What the sentence should look like? Please help me, any comments will be appreciated! Thank you.

 

[CJ_London]

sounds like this is a test, not a real life situation

you need to modify your query to cope with a 6 month interval rather than 12

so a partial example would change

iif([FrequencyInMonths]<24,12,[

to what I think should be

iif([FrequencyInMonths]<18,6,[

 

[tsvetkovd]

Hi, yes, it is an exercise , for me. But its getting complicated :banghead:. Since I need to apply those changes in the inspection frequency for a list of component types on bridges of selected construction types. And this should apply all updates for a particular review date to all affected bridge components in a single run of the procedure. There is table "ConstructionTypes", where I have the variety of the construction types (with rows cable-stayed, suspension, arch-bridge etc. ) and table "Components" with all the components applying to the construction types(with rows cables, pylons, decks, piers etc.). There is also table "Inspections" in the database with the "ConditionCode", "Date" and "FrequencyInMonths" columns. They are all in a relationship with primary and foreign keys. For sure the iif statement will help, but how can I get all these together and form one or more statements to get the result needed? I wonder if its possible? For instance: If the "ConditionCode" of "Cables" on construction type "Cable-stayed" bridge is bad or more than 3 increase "InspectionFrequency" by 6 if not decrease by 6. Is something similar possible? Thnak you for your time!

 

[CJ_London]

With respect, I'm here to help people with real life issues - and I don't do homework because it defeats the objective of learning. What you ask is certainly possible, plan it out as a formula and then convert it into SQL

 

[tsvetkovd]

Thank you, fair enough. Appreciate it. You helped me more than enough. I need to read more about how to form the sentences in the queries, will you please give some links providing that information.

 

[CJ_London]

Here is a link to SQL

http://www.w3schools.com/sql/sql_functions.asp

For things like iif, nz etc these are called functions and very similar to what you will find in Excel, the best way to learn about them is to type into your search engine - e.g. 'access iif'. The MSDN sites are usually technically pretty good, if a bit dry.

you can also try this site
http://office.microsoft.com/en-gb/a...pressions-and-other-criteria-RZ010361794.aspx

If you want a reasonably full list of functions probably the easiest thing to do is use the experssion builder, select functions and then a category - you'll find iif under the program flow category

Good luck!

 

[tsvetkovd]

I think I figured out what my IIF statement should look like, if it's possible. Although it may look stupid as iif sentence these are the conditions I need to follow. Something like this: Next Inspection: iif ([ConditionCode] of [Components] of [TrussGirder]and[ArchBridge]> 3), ([NextInspection]=[DateTime]+[FrequencyInMonths]-6 but not less than 6), ([NextInspection]=[DataTime]+[FrequencyInMonths]+6 but not more than 36 ))). Please help, if anyone knows how can I write it as a proper iif statement. Thank you!