Need help creating specific calculation/function for list/report layout set-up
I have a very specific problem/question:
I'm creating a list/report layout, grouped for subtotals and grand totals. I have a field in my database that is called "Life Remaining" and shows a number. Each record in the database is for a difference piece of equipment, and the Life Remaining field references how much life expectancy is remaining in that piece of equipment. I want to be able to sort my list/report into three groups: Life Remaining is equal to or less than One Year, Life Remaining is between One and Five Years, and Life Remaining is Greater than Five Years. Each of these groups would show a sub-total of the Estimated Cost of Replacement (another field in each record).
I know how to set up the list to create subtotals (I have done so in another layout to sort according to Priority Number), but I can't figure out what kind of calculation or function will give me an answer in a single field that can then be used to sort the list. I think what I want to do is have a field that has a function which would be something like (in layman terms):
Life Remaining equals which of the following: (Equal to or less than 0), (Between 0 and 5), and (Greater than 5)
This would give me a single field which then shows one of the following:
"< 0" (meaning Life Remaining is equal to or less than 0), "> 0, < 5" (meaning Life Remaining is between 0 and 5) or "> 5" (meaning Life Remaining is greater than 5) - or something similar.
I could then use this new field as my choice when I "Organize Records by Category" in the layout set-up. This would give me the subtotals I need.
If anyone has ANY hints or suggestions, or just knows how to create a function like that, I'd love your help! It's been a while since high school math and I'm just hitting a dead end. Let me know if you need more clarification.
Thanks so much!