Defined ranges in a calculated field

[Yeti]

Hi guys - you were very helpful last time, so I'm back for more



I'm trying to create a calculated field in a query that will allow me to group numbers into irregular categories.

My field is called "Experience" and it contains the number of years of experience an employee had in the field before being hired by this particular company. I'd like a new field that contains the text values "0-3," "4-9," "10-19," and "20+"

I know how to set up the query to show me the number of employees per group, but I don't know how, in design view, to define my calculated field.

Thanks!

[RuralGuy]

Have you considered creating a User Defined Function (UDF) in a standard module for this task?

[Yeti]

I haven't, and I'm not familiar with those. I can read up on them if you think they'd be a better option in this case. Where would I use this function?

[RuralGuy]

Your query would have a field similar to: YourFieldName: = ReturnGroup([Experience])

[ajetrumpet]

As an alternative to writing code, you could also create a "Ratings" table, put your experience criterias in one column there, then just simply write in a query:

SELECT [category], 

COUNT(DCOUNT("experience", "employees",

"[experience] = '" & [category] & "'"))

FROM ...

GROUP BY [category]

Another thing too, is that you can simply add a category table for later use. This is advantage because you can throw in a one-to-many relationship with the employees table joining on "experience" or "category". Then in the future, all you have to do is query out the two tables with one inner join connecting them.

Code works too, and is just as easy as going through the headaches of creating tables and making relay's.