If statement in an update query

[tsvetkovdimitar]

Please anyone tell me how to use if statement or how to do the following update in an update query: for instance, If the values from column Sales are less than 3, subtract 6 from the values in column Orders. Each column is in a separate table. Thank you!

[June7]

Saving calculated values usually a bad idea. Without a better understanding of data structure, really hard to advise.

[tsvetkovdimitar]

I know it's a bad idea, but thats the way it should be done for now. Actually it will be if sales > 3, orders - 6, if sales < 3, orders + 6. But I still don't know how to do that.

[June7]

You want to create a new record in a table based on some value in Sales column of current record on form?

Code in some VBA event procedure, maybe a button Click or some textbox AfterUpdate.

CurrentDb.Execute "INSERT INTO tablename (Orders) VALUES(" & IIf(Me.Sales >3, 6, -6) & ")"

As I said, without knowing your structure, hard to advise with specifics.

[tsvetkovdimitar]

Sorry for not specifying, that has to be in a query using two tables, one has the attribute sales and the other has the orders. Good information though, I just learned something new Thank you!

[tsvetkovdimitar]

Hi again . I hope this time I am providing enough information about what my query is. 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!

[June7]

Still don't understand data structure enough.

An UPDATE sql action can modify data for all records that meet a given criteria, such as a bridge ID.

CurrentDb.Execute "UPDATE Inspections SET FrequencyInMonths=FrequencyInMonths +" & IIf(ConditionCode>=4, 6, -6) & " WHERE BridgeID = " & Me.BridgeID

The real trick is figuring out what event to put code in and preventing the code from being run multiple times.

If a component has a frequency of 4 months, how could it be decreased by 6?

CurrentDb.Execute "UPDATE Inspections SET FrequencyInMonths=FrequencyInMonths + " & IIf(FrequencyInMonths < 7, 0, IIf(ConditionCode>=4, 6, -6)) & " WHERE BridgeID = " & Me.BridgeID

[tsvetkovdimitar]

Thank you June :-). Sorry I am still not good in explaining those things (probably because I am new in database). Just say what else you need to know about the data structure, so I can explain it or even post a snapshot. Thank you :-)

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

[Dal Jeanis]

If you uploaded a PNG of the database relationships window for just the relevant tables, that would help. Otherwise, description of the tables in something like this format would help:

ConstructionTypes
   ConstrType         PK Autonumber
   ConstrTypeName     Text

Inspections
   InspectionID        PK autonumber 
   ComponentID         FK to Components
   ConditionCode       Number
   InspectionDate      Date           
   FrequencyInMonths   Number

Bridges
   BridgeID            PK autonumber 
   BridgeType          FK to ConstructionTypes
   BridgeName          Text

Components
   ComponentID         PK, autokey
   BridgeID            FK to Bridges
   CompTYpe            FK to some other table

By the way, Date is a reserved word and should never be used by itself as a field name. Same with Year, Day, Month, Name, and a whole lot of other common terms.

[tsvetkovdimitar]

Here is the database file and the relationship diagram. I hope it will give you the information that you need. Thank you.

[June7]

I think inspection relationships are wrong.

If components can have different inspection frequency for each bridge then seems to me the mandated inspection frequency for each component should be in the Components table.

Then there should be a table for each inspection event. Assuming each inspection can involve multiple components with individual conditions, then might also want a table for InspectionDetails.

[tsvetkovdimitar]

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 [BridgeType]> 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!

[June7]

Try:

NextInspection: IIf(ConditionCode>3, DateAdd("m", IIf(FrequencyMonths-6<6,0,FrequencyMonths-6), DateTime), DateAdd("m", IIf(FrequencyMonths+6>36,0,FrequencyMonths+6), DateTime))

The way this database is set up, cannot keep history of inspection events - only the most recent. Do you want historical records?

[tsvetkovdimitar]

I tried that sentence and this popped up:. Yes I do want historical records. Why isnt going to keep history of inspection events? What is wrong with it? I test it with all the possible ways in access and it says that everything is ok. Please give me more details about that sentence. I also need to include in that only selected component types of selected bridge types. For instance if the condition code of the deck and the railings of the arch bridge and the truss girder bridge is more than 3 (which means their condition is bad) the inspection interval should be decreased by 6 months if not increased by 6 and show when the next inspection should be. Or this sounds stupid I suppose