I'm trying to run an update query to input a category based on a certain column.

For example column Seniority has numbers 0-25
I want to update column Level (currently blank) so that it shows what level they are in (e.g. Level 1 for 0-3 yrs seniority).

Any idea how to do this? I was trying use expression builder in the "update to" field in update query, but I'm not sure what to write...

Please please help!

Start off with a query to just select the items to be updated.

Once you have that definitively worked out, you can add the update part, as you described.

You can either create one query for all updates (in which case you'd need to use an 'if' statement in the 'Update To part) or run it once for each group (perhaps the most time consuming, but the easiest way to go).

Thanks!

I just want to update the Level column. How exactly would I form the if statement in the update to field? Sorry I'm pretty new at this..

Thanks again

Difficult to say precisely without knowing the table structures and the thresholds for each level.

Assuming you've got your query to select the Seniority and Level columns. In the 'Update To' row of the query for the Level column you'd write something like

IiI(Seniority > 25, 10, IIf(Seniority > 22, 9, 8))

This is saying:
If the Seniority is greater than 25, set the Level to 10.
Otherwise, if the Seniority is greater than 22, set the Level to 9.
Otherwise, set the Seniority to 8.

Obviously, you wouldn't want all other Level values to be 8, but hopefully you can see the way the IIf statement is being created. Just get the ranges clear in yoru own mind and add as many sections as you need.

Thanks! I tried to write a simple version just to see if it works, but I get an error saying data type mismatch:

iIi( [Teachers]![Seniority] < 4, 1, IIf( [Teachers]![Seniority] > 3, 2)

Thoughts?

I also don't know how to specifiy if Senority is between 5 and 10, for example, update level to 3.

Thanks again! I really really appreciate the help

I didn't ask, but should have, what the data types are.

If both the Seniority and Level are numbers, that should work. However, if one of them is actually text, you'll need to put quotation marks around it.

e.g.
if Level is a number and Seniority is text

iIi( [Teachers]![Seniority] < '4', 1, IIf( [Teachers]![Seniority] > '3', 2)


if Level is text and Seniority is a number

iIi( [Teachers]![Seniority] < 4, '1', IIf( [Teachers]![Seniority] > 3, '2')


if both are text

iIi( [Teachers]![Seniority] < '4', '1', IIf( [Teachers]![Seniority] > '3', '2')


Also, it looks like you've used III for the first if statement, not IIF.

They are actually both numbers . I changed it to iif but I am still getting data type mismatch error

IIf( [Teachers]![Seniority] < 4, 1, IIf( [Teachers]![Seniority] > 3, 2)

This is supposed to go into the "update to" field right? Any idea what I'm doing wrong?

At first glance, I think there's a closing parenthesis missing from the end of the statement.

Nothing springs to mind apart from that, but as soon as I finish running what I'm currently running (hence the time available to log onto the forum), I'll create a dummy table and test it out.

Edit: Sorry, in answer to your question, yes it's supposed to go there.

May have it.

Try the following.

IIf([Seniority] < 4, 1, IIf([Seniority] > 3, 2))

It worked!!! Thank you so so so much :)

Good to hear. Glad I could help.:)