If statement in an update query

[June7]

See post 12 regarding history data.



To maintain inspection history, an InspectionDetails table would have the ComponentID saved as foreign key. Saving the InspectionID as foreign key in Components table allows only one inspection for each component.

UPDATE query will need criteria in a WHERE clause to restrict the records that will be affected.

I think you need to better understand relational database principles and also better define your data entities and relationships. Maybe tutorials here will help http://www.rogersaccesslibrary.com/

[tsvetkovdimitar]

Thank you, I red more about the matter today, regarding your previous post. Now I know (partly) where my mistake is. I am wondering what else should I move in the table InspectionDetails and how to make the relationship between them?

[tsvetkovdimitar]

This is how my ERD looks like now. I think that if not completely now is more closer to what you advised me to do. If that is the diagram, how can I make the iif statement to execute the following conditions: If [ConditionCode]of[Towers]and[Cables]and[Piers]of[Cable-stayed]and[Suspension]>=3 then [IntervalMonths]and[InspectionDate]-6 but not less than 6, else [IntervalMonths]and[InspectionDate]+6 but not more than 36. It won't look like that for sure, it just gives idea of the conditions. That is the way I see it if the inspection frequency has to be changed for a list of component types on bridges of selected construction types. And then 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. Or if I have 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 months. 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. It will be great if give some information how can I write more complex iif statements, combining multiple tables and columns and rows. Because all I found is just simple explanation of the statements and combining some few fields. Thank you for your time!

[June7]

Sorry, still doesn't look right to me. And is becoming more apparent that I just don't know enough about your business process.

For instance, how do you identify 'components' - do they have serial numbers? What is a 'component' - nuts, bolts, cables, beams? Does each component for each bridge have its own inspection frequency, independent of the same type of component on another or even the same bridge? Why would a component have a quantity attribute?

Your update parameters are so complex I am not sure they can be managed by query.

[tsvetkovdimitar]

Yes, the components have unique code. Component is a pier, railing, cable, tower, deck. The cables on the suspension bridge for example consist of helical steel strands each one of them could as long as 300 meters. These cables run between towers and are anchored in abutments at each end. The towers and the abutments are built of reinforced concrete and steel, so the components are quite big and consist of more materials and parts. Components have quantity, because cables, could be 20-30 or 50, piers could be 2-4-6, towers could be 2-3-4. Thus each component for each bridge has its own inspection frequency, each component of each bridge is different from one another for many reasons(location, weather conditions, traffic and etc.) I hope you have a better idea of my database now. Thank you for your interest to help me :-). About the complexity of the update I will try to simplify it as much as I can if you say that this is not possible with an update query. Many thanks:-)

[tsvetkovdimitar]

I wrote that IIf([ConditionCode]>=3,IIf([IntervalMonths]<6,12,[IntervalMonths]![InspectionDate]-12),IIf([IntervalMonths]>6,36,[IntervalMonths]![InspectionDate]+12)) but it doesn't work. What is wrong with it?

[June7]

So you are saying a single 'component' could actually be 50 cables? The component ID would be assigned to this group of 50 cables?

That is not an expression I suggested.

[tsvetkovdimitar]

Yes, all 50 cables are considered as one component and all of them have the same code for the bridge they are installed to. That is not an expression, I tried to write something....

[June7]

Inspections are conducted on components according to their individual frequency, not the entire bridge end-to-end? So you might inspect only cables this month because that's all that's due but then piers are due for inspection 3 months later?

Consider:

tblBridges
BridgeID

tblBridgeComponents
ComponentID
BridgeID
ComponentType
Quantity
InspectionFrequency

tblInspections
InspectionID
InspectionDate
InspectorID
ComponentID
ConditionCode

The inspections table could be two tables if there will be many inspections by the same inspector on the same date and you don't want a lot of repetition of that info. This is the table for inspection history. If you want to know the most recent condition code of a component, retrieve the latest record for it.

If the frequency for a component needs to be modified because of it's condition code, then that should be done when condition record is created for that component.

The next inspection date will be calculated based on the last inspection date and the frequency interval.

[Dal Jeanis]

June's comments are very astute. It seems more manageable to have a next-inspection-frequency on a different table by component type and condition, rather than adding or subtracting to some base frequency.

tblInspectionLagByCondition
   BridgeType         Number  FK BridgeType
   ComponentType      Number  FK ComponentType
   ConditionCode      Number  FK Condition
   LagMonths          Number    (in months to next inspection)

Then you can calculate the next inspection date by taking the latest inspection date for any given component, and joining to the InspectionLag table on that component's type and condition, to calculate the date of next inspection.

By the way, your relationship diagram in post #18 is missing the table for component types, many to one from Code in the Components table.

[tsvetkovdimitar]

Many thanks for your comments, really thank you people for your time, I appreciate that. I got the idea for a new table for inspections and I will do that. Just would like to ask you one last thing. Assume that all components on all bridges have already had an inspection, the dates are recorded, inspector's comments are recorded the condition codes are recorded, only the frequency needs to be changed based on the condition code recorded, hence the next date of inspection. Here is the time to use the new table for inspection, right? Then assume I would like to increase the inspection frequency on the decks on the beam bridges and on the upper chord on the truss bridges by 6 months(the result should not be less than 6 months) and change the date of the next inspection by adding 6 months to the date of the previous inspection. This should just update the last inspections by 6 months and record only the two new dates for the two relevant components on these two different types. Is that possible to be done with this database or it's mandatory that I need to make a new table to do that? Thank you!

[Dal Jeanis]

If you have the date of last inspection for each component, and the condition of each component at that date, then you can **calculate** the date of next inspection. You don't need to store it.

If you want to store that calculated date in the database, that's fine. It doesn't violate normalization. That next inspection date can go on the component record itself. Just make sure that you only update it based upon the latest inspection for that component.

If you want to write twenty or thirty different individual queries that each say, If the bridge type is THIS, and the ComponentType is THIS, and the Condition CODe is THIS, then update the next inspection date to be equal to the last inspection date plus THIS NUMBER OF MONTHS, then you can do that.

Or, you can do it the easy way and set up one single table that has that inspection rule information, and do the update or calculation once.

Let me ask the question as clearly as I can:

What is the business rule that decides what the inspection interval rule is for any given bridge? I understand that it is based upon some kind of historical experience with the bridge, but where does that experience come from?

[June7]

It is my understanding that inspection frequency for components are different for each component type and for each bridge. There may be a minimum and maximum frequency for each type of component but each component on each bridge can have have frequency different from the same component type on another bridge. Therefore, my suggestion is to have frequency be an attribute of each component on each bridge. Adjust the frequency as necessary when the component is inspected. Run a report that calculates next inspection dates, no need to save.

[tsvetkovdimitar]

Deal jeanis, the manufacturer decides the frequency, but then it is changed due to the condition changes.
Yes June that is the situation and I have the frequency for each component on each bridge. For now I will need to do is the above mentioned procedure with the two components of the different bridge types. Please tell me, is that possible? And what will the sentence look like? Thank you!

[June7]

Trying to modify the frequency or calculate next inspection date for multiple components with variable parameters may be too complex, at least for me, to be done in a single UPDATE action.

If you just do a blanket update for specific bridge components without considering the latest inspected condition nor minimum/maximum limits, simply:

UPDATE tblBridgeComponents SET InspectionFrequency = [input interval in months] WHERE BridgeID = [input bridge ID] AND ([ComponentType]=[input component type 1] OR [ComponentType]=[input component type 2]);

Now if you need conditional IIf for the frequency adjustment to consider the latest condition code for each component and also not allow the adjustment to go outside minimum or maximum allowable for each component type - well, I hope you get the idea. Just too complicated. I would probably build a VBA procedure.

We just don't know enough about the rules.