Combining falues into one in a query

[Peterz7]

Hi all,
I've got a quick question I'm afraid for which there is no solution but I thought I'd try.....



I'm summing transaction records for a given time period in a query, by a specific value (that comes from a system I cannot change)

Values (for Region) are: East, West, North, South, Other, Misc, Unknown and occasionally NULL

What I'd like for a result is totals for East, West, North, South, and to combine "Other", "Misc", Unknown" and NULL into one grouping, say with the name "Other"

Has anyone run into this problem? Is there solution for this? Without being able to modify production data, I'm brainstorming other workarounds if this is not possible within the summation query.

Thanks for reading this. I appreciate any suggestions any of you may have.
-Peter

[CJ_London]

use a group by query, and rather than grouping on 'Region', group on a calculated value, something like


RegionGrp: iif(nz(region,"Other") in ("Other", "Misc", "Unknown"),"Other",Region)

[Peterz7]

use a group by query, and rather than grouping on 'Region', group on a calculated value, something like


RegionGrp: iif(nz(region,"Other") in ("Other", "Misc", "Unknown"),"Other",Region)

This worked! Thank you so much, I really appreciate your help. You're awesome!