If statement in an update query

[Dal Jeanis]

Given this layout:

tblBridge
   BridgeID           PK
   BridgeName         Text
   BridgeType         Number (FK BridgeType)

tblComponent
   ComponentID        PK
   BridgeID           Number (FK tblBridge)
   ComponentType      Number (FK tblComponentType)
   LastInspected      Date
   Condition      
   NextInspection     Date
   FreqRecommended    Number (recommended inspection frequency in months)

tblComponentType
   ComponentType      PK
   CompTypeName       Text

tblInspectionLagByCondition
   BridgeTypeCD     Number  (FK tblBridgeType)
   ComponentType      Number  (FK tblComponentType)
   Condition      
   MinMonths          Number  (lowest number)
   CondFreqOffset     Number  (change to manufacturer's recommendations))
   Maxmonths          Number  (highest number)

This query would calculate the next inspection date:

SELECT 
   TC.BridgeID,
   TB.BridgeType,   
   TC.ComponentID,
   TC.ComponentType,
   TC.LastInspected,
   TC.Condition,
   TC.FreqRecommended,
   TL.CondFreqOffset,
   TL.MinMonths,
   TL.MaxMonths,
   (TC.FreqRecommended + TL.CondFreqOffset) AS CalcFreq1,
   IIF(calcFreq1 > MaxMonths, MaxMonths, IIF(calcFreq1 < MinMonths, MinMonths, calcFreq1) As CalcFreq2
   DateAdd("m",calcFreq2,TC.LastInspected) As NextInspDate
FROM
   (  (tblComponent AS TC 
       INNER JOIN tblBridge AS TB
       ON TC.BridgeID = TC.BridgeID)
    INNER JOIN tblInspectionLagByCondition As TL
    ON TB.BridgeType = TL.BridgeType
    AND TC.ComponentType = TL.ComponentType
    AND TC.Condition = TL.Condition
   );

[tsvetkovdimitar]

I wish I can help you with anything :-). I will try to brake it down and adjust it to my tables, without the table InspectionLagByCondition. Thanks again!

[Dal Jeanis]

Sure. Hope the example helps you work it out.

[tsvetkovdimitar]

I guess I have to look for another solution to my problem. Because what I want seems to be impossible with access or in database at all. Because for now they just want me to update the inspections of the upper chord of bridge type truss bridge and the deck (or I can use the Code of the component instead of names, if its easier) of bridge type beam bridge by increasing their frequency by 6 months and adding 6 months(but not less than 6) to dates those components have been visited. Probably the complicity is coming from the fact that component type upper chord and component type deck are part of field Names of table Components and the bridge types are part of field TypeName in table BridgeTypes and FrequencyInMonths is a field of table Inspections. The InspectionDate is field from another table - InspectionDetails. I probably have to amend the database, but I don't know so I can do this update, or I have to give up of using access ? I will see what I can do. Thank you guys you helped me a lot, I have now a better idea, at least I think I have :-)

[Dal Jeanis]

If I were you, I would back up the database someplace safe before the update, because this whole procedure is quite risky.

The other strategy that you can try is to create a select that finds all the components that need to be updated, and copies them into a work table. Then, you can determine the new dates within the work table, and then finally, you can do a simple update query to overwrite the original table.

If you post the exact format of the tables, and the exact values they need to have in order to cause an update, then we can help you work it out.

Also, are you saying they are telling you to change the dates the bridges have already been visited? The inspection dates? That sounds shady.

[tsvetkovdimitar]

These pictures will be sufficient I think. So what I need is to increase the FrequencyInMonths for the Upper chord on the Archbridge and for the Foundation and Bridge seats on the BeamBridge by 6 months(but not less than 6) and to get dates for the next inspections by adding the frequency to the dates of the passed inspections for the above mentioned components. And if its possible to decrease FrequencyInMonths for the Piers and Columns on the Archbridge and Deck and Reailings on the Beambridge using the same condition. If you need more details about the database I will give you whatever you need. Thanks for your time