Access 2013 Nested IIF Update Query Help

[Mac1206]

I have 2 Tables with a one-to-one join. (A)Price_Tbl and (B)Freight_Tbl which I need to update (A)Tbl.Cost_Fld....
IIF (A)Price_Tbl.Cond_Fld = "Truck", "Not Assigned", "Truck with Tarp"---pull the rate from (B)Freight_Tbl.Rate_40,
IIF (A)Price_Tbl.Cond_Fld = "Train", "Railway", "Flat Car"---pull the rate from (B)Freight_Tb.Rate_RR
IIF (A)Price_Tbl.Cond_Fld = "Multimodal"---pull the rate from (B)Freight_Tb.Rate_MM
Then multiply everything 20

Thanks in advance for your possible help....

 

[xenou]

It would be easiest to write 3 update queries and run them in turn (one for the condition "Truck", "Not Assigned", "Truck with Tarp", another for the condition, "Train", "Railway", "Flat Car", and a third for the condition "Multimodal").

Then its simple:
UPDATE X SET [Cost_Fld]= [Rate_40] * [whatever] * 20 WHERE [Cond_Fld] in ("Truck", "Not Assigned", ...)
UPDATE X SET [Cost_Fld]= [Rate_RR] * [whatever] * 20 WHERE [Cond_Fld] in ("Train", ...)
UPDATE X SET [Cost_Fld]= [Rate_MM] * [whatever] * 20 WHERE [Cond_Fld] in ("Multimodal", ...)


Your rate table is not normalized. It should have two columns, Rate and RateType. Then you could join properly on the condition field and the rate type field.

 

[Mac1206]

It would be easiest to write 3 update queries and run them in turn (one for the condition "Truck", "Not Assigned", "Truck with Tarp", another for the condition, "Train", "Railway", "Flat Car", and a third for the condition "Multimodal").

Then its simple:
UPDATE X SET [Cost_Fld]= [Rate_40] * [whatever] * 20 WHERE [Cond_Fld] in ("Truck", "Not Assigned", ...)
UPDATE X SET [Cost_Fld]= [Rate_RR] * [whatever] * 20 WHERE [Cond_Fld] in ("Train", ...)
UPDATE X SET [Cost_Fld]= [Rate_MM] * [whatever] * 20 WHERE [Cond_Fld] in ("Multimodal", ...)


Your rate table is not normalized. It should have two columns, Rate and RateType. Then you could join properly on the condition field and the rate type field.

You are correct but the goal is to see if it can be nested into 1 query since this will be an ongoing process and built into a macro...Thanks

 

[Mac1206]

You are correct but the goal is to see if it can be nested into 1 query since this will be an ongoing process and built into a macro...

Is it possible to nest queries into one via iif or switch??? If so, how?....Thanks in advance

 

[welshgasman]

You are correct but the goal is to see if it can be nested into 1 query since this will be an ongoing process and built into a macro...Thanks

A macro can run all those queries in sequence.


I'd write a function to what you want if it had to be one query only

 

[Mac1206]

Is it possible to nest queries into one via iif or switch??? If so, how?....Thanks in advance...

Thanks for the input welshgasman, could you please provide an example of how you would write it using the scenario tables which I provided...Thanks in advance

 

[xenou]

Nested IIFs would work pretty much as advertised:
IIF(A, A-True, IIF(B, B-True,IIF(C, C-True, C-False)))

Note that if you reach C-False, or the final failed condition, then nothing has passsed any true test, so that can be considered a "default" result, whatever that should be. Sometimes it's a case that really should never occur so pick a value that would indicate failure (such as NULL or -1).

 

[welshgasman]

Thanks for the input welshgasman, could you please provide an example of how you would write it using the scenario tables which I provided...Thanks in advance

Something along the lines of the code below. Syntax for Dlookup might not be exact.
Then in the query VehCost:VehicleCost(Cond_Fld)

As mentioned you are jumping through these hoops due to your table not being normalised.

Public Function VehicleCost(strVehicle As String)
Dim curRate As Currency


Select Case strVehicle
    Case "Truck", "Not Assigned", "Truck with Tarp"
        curRate = DLookup("Rate_40", "Freight_tbl", "Cond_Fld= '" & strVehicle & "'")
    Case "Train", "Railway", "Flat Car"
        curRate = DLookup("Rate_RR", "Freight_tbl", "Cond_Fld= '" & strVehicle & "'")
    Case "Multimodal"
        curRate = DLookup("Rate_MM", "Freight_tbl", "Cond_Fld= '" & strVehicle & "'")
End Select
VehicleCost = curRate * 20


End Function

 

[Mac1206]

Something along the lines of the code below. Syntax for Dlookup might not be exact.
Then in the query VehCost:VehicleCost(Cond_Fld)

As mentioned you are jumping through these hoops due to your table not being normalised.

Public Function VehicleCost(strVehicle As String)
Dim curRate As Currency


Select Case strVehicle
    Case "Truck", "Not Assigned", "Truck with Tarp"
        curRate = DLookup("Rate_40", "Freight_tbl", "Cond_Fld= '" & strVehicle & "'")
    Case "Train", "Railway", "Flat Car"
        curRate = DLookup("Rate_RR", "Freight_tbl", "Cond_Fld= '" & strVehicle & "'")
    Case "Multimodal"
        curRate = DLookup("Rate_MM", "Freight_tbl", "Cond_Fld= '" & strVehicle & "'")
End Select
VehicleCost = curRate * 20


End Function

Thanks and I understand but can you write a DLookup in Access 2013 as such, in a Dim Statement???

 

[xenou]

Thanks and I understand but can you write a DLookup in Access 2013 as such, in a Dim Statement???

Yes, you can. But you said you wanted to use macros so a VBA function may not be what you are looking for.

Edit: What I mean is, that people who use macros (mainly) generally do not want to involve VBA in their applications. Otherwise, they'd just use vba and forget the macros. VBA can always do the same, and more, than the macros, but the security context is different so they are not apples to apples.