Hi,

Can someone pls. assist me with a query I would like to write in Access. The objective of the query is to categorize yearly data into specific term buckets.



Examples noted below:
Term= 3.8 yrs >> Expected result= "< 5 yrs"
Term= 7.9 yrs >> Expected result= "10 yrs"

I wrote the following query but it does not work:
IIf(Is Null ([Term])=Is Null, " ", (IIf([Term]<=5), "< 5 yrs",(IIf([Term]>5 AND ([Term])<=7),"7 yrs", (IIf([Term]>7 AND ([Term])<=10),"10 yrs", (IIf([Term]>10 AND ([Term])<=15),"15 yrs","> 15 yrs"))

I appreciate your assistance in this matter.

I would go with a lookup table instead.

Any reason you can't do that?

Hi Teddy,

Can you pls. elaborate on the lookup table? Are you referring to creating a seperate table in Access that would contain the term buckets and join this table with the original table?

Thank you

When I count the round brackets in your expression, I got 13 open and 10 closed. I suspect you should have the same number each way.

If you write it out on a bit of paper, and for each open round bracket, label it with a letter (so, first one is a, second one is b...), and then find what you believe is the closing round bracket for a and label it a, and the same for b and so on, you should see where it is out of step. The format of IIF is IIF(expr,truepart,falsepart) - balanced round brackets.

I hope this is some help to you.

Precisely.

This approach would also grant you better flexibility for reporting as you can sort/group/sum/etc on those buckets while getting friendly names at the same time "for free".