query to create new category labels

[electricbanana]

Hello, I've been working with access for some time now on and off but never really got to grips with functions.

I have a table with one field containing numeric data and an adjacent field with text data. What I want to do is create a calculated field to create a set of categories. I want to say if the scooter is under 125 then give it this label.

ie,



My current solution is just to run a few update queries but I'd like to leave this a little more automated.

Many thanks
eb

 

[wiklendt]

it's impossible to develop a method because you have not explained your algorithm. that is, (at least to me) your picture does not show any obvious pattern of logic in categorisation.

i am assuming, of course, that this table is only a portion of your data (that is, it would be easier to just type the category in rather than generate code to do it for four items).

for a start - is "125" a dollar value? or an arbitrary value - will all items have a number and will this number only consist of integers (i only ask this because your categories appear to be alpha-numeric).

 

[electricbanana]

Thanks for your reply Wiklendt,
1. the numeric value will be only integers, its basically engine displacement.
2. the left 2 columns show data thats in one of my tables and the 3rd column is what I want to calculate with the query.
3. The algorithm: If <125 and scooter then SC125, if less than 125 and not scooter then MC125, if more than 125 and scooter then scooter, if more than 125 and not scooter then MC.

I can use the IF with numeric data but not with text or a combination of both.

Hope I've made that a little more clear.

 

[raskew]

Hi -

You can use the Switch() function in a calculated field. Copy/paste this to a new field in your query, substituting your [field names] as appropriate.

Switch([[COLOR="Red"]numfld[/COLOR]]< 125 and [[COLOR="Red"]txtfld[/COLOR]]= "scooter", "SC125", [[COLOR="Red"]numfld[/COLOR]]< 125 and [[COLOR="Red"]txtfld[/COLOR]]<> "scooter", "MC125", [[COLOR="Red"]numfld[/COLOR]]> 125 and [[COLOR="Red"]txtfld[/COLOR]]= "scooter", "SC", True, "MC")

Note that your algorithm disagrees with the .jpg example for [txtfld]>125 and [txtfld]<>"scooter"

HTH - Bob

 

[electricbanana]

Hi Bob,

Many thanks for that. It does the job and I've learned sth new.

thanks again

eb