Update Query using SWITCH function

[CJSpinner]

Disclaimer: I must be the dumbest person trying to use Access.

I am trying to update a column [Stratification] with different text values depending on what is found in another column on the same record/table.
Table is called [YearsOfQuotes]

Desired result is to look at the GM% (Quote) column on the record, and then put in >30 , >20 or Other in the Stratification column

This format keeps coming back with a name is not valid, but I borrowed this exact code from another video, and it actually works when I'm trying to create a new table, but I can't figure out how to make it work inside an Update Query.

 

[Gasman]

I think you have them backwards?
The field is stratification and you updatet o whatever the Switch produces.
SO how it worked before I have no idea?

Try looking at the SQL and see if that makes any sense.

 

[Mark_]

Desired result is to look at the GM% (Quote) column on the record, and then put in >30 , >20 or Other in the Stratification column

If you are calculating a value, you normally don't save the calculated value.
Is there an expectation that this calculation could be changed? If yes, do you need all values to reflect the new calculation OR do you need to keep separate values for past records? Only time you would really want to save this is if the business rule is subject to change AND you need to record what was used at the time the transaction was processed. Alternately if you have a regulatory requirement to save this, then you save this.

 

[theDBguy]

Hi. Welcome to AWF!

I agree. You are trying to update a calculation into whatever is in the field. Besides, it's not recommended to store calculated values anyway.

 

[CJSpinner]

Gasman,
I didn't quite understand what you were saying was backward, but it did lead me to the solution. Thank you.

 

[CJSpinner]

Hi. Welcome to AWF!

I agree. You are trying to update a calculation into whatever is in the field. Besides, it's not recommended to store calculated values anyway.

Okay, I'm a total newbie. So what's the recommendation to storing a calculated value?
I will say that I'm refreshing my data from an external source on each call.

 

[Gasman]

That is exactly what I was saying. You had the values backwards, each should be in the others position.
Now as others have said, whether you should be storing this is another matter.

 

[Mike Krailo]

Okay, I'm a total newbie. So what's the recommendation to storing a calculated value?

Don't do it. If you have all the raw data already in your existing table, you never add calculated values to the table. If those values can be calculated, you can do the calculations in a report, or show the calculations on a form, but just don't store them in the table. You can create a query that has fields that have calculated data in them, that's acceptable as long as your not saving it to the table.

 

[June7]

Strongly advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention.

 

[Pat Hartman]

Disclaimer: I must be the dumbest person trying to use Access.

Not by a long shot

Everything the others said you should take as gospel. In the cases where you want to display the string, use the Switch() in the form or report's ControlSource for the field you are displaying or create a conversion table and use dLookup() to return the value. The conversion table is far easier to maintain going forward. If you want to add a new value, you just add a row to the table. Using Switch() you need to find every instance where you've used the Switch() and change each one separately. Given that, I personally never use Switch().

 

[DHookom]

An alternative to the Switch() function and a lookup table, you could create a small user defined function that accepts the field value and returns the appropriate value. All of your logic would be in one place where it can be commented and edited quite easily. Save the function in a module named modBusinessCalcs. You can call the function from any where you would use a function such as a control source, in a query, or in code.

I still prefer a simple lookup table which could be accessed in the function.

 

[The_Doc_Man]

The simplest way to put this (regarding the advise of "Don't store a calculated field") is to save space. You said that the field with the ugly name [GM% (Quote)] is in the table. Store that - although I also agree make the name simpler, like GMPctQuote, but then take that SWITCH statement and put it as a field in a query, perhaps with an alias name (using ... AS QuoteRange...) so that you compute that value only when you need it. And for those queries that DON'T need the computation, don't include the computed field.

Queries are your friends. Let them do the work for you. Including the idea that they can recompute something if the basis of the computation is readily available. In this case, the GM%(Quote) being stored means you can use that formula when you need it.

Before you ask the question, we know that you can see the raw data in the table, but one of the purposes of a query is to reformat, recompute, or convert data for you to display it. In a form or report, you can also compute a calculated field by loading the control's .ControlSource with the formula. Again, the point is to not store something if you can repeatably compute it.

Now, the other side of that coin... IF the computation depends on more than one input and one of the inputs varies over time, then you WOULD store the computed value based on the then-current inputs... OR you would store the current values of the formula inputs to do the computation later. There it just depends on what else you are doing as to which is better.

 

[DickyP]

The real problem with using Switch() (especially in a query) is that it is highly inefficient. Unlike Select Case EVERY condition is evaluated EVERY time it is used, plus it returns Null (often thus generating errors) if no criteria are matched.

 

[GPGeorge]

Disclaimer: I must be the dumbest person trying to use Access.

Spoiler:

I am trying to update a column [Stratification] with different text values depending on what is found in another column on the same record/table.
Table is called [YearsOfQuotes]

Desired result is to look at the GM% (Quote) column on the record, and then put in >30 , >20 or Other in the Stratification column

View attachment 119149

This format keeps coming back with a name is not valid, but I borrowed this exact code from another video, and it actually works when I'm trying to create a new table, but I can't figure out how to make it work inside an Update Query.

Nope. You are smart enough to ask for advice. That makes you far, far from the dumbest person....