Query/if statement

[hiwelcome]

Hello,

Right now, I have a query pulling a bunch of data for a report by UnitName. I would like to group some of these units for display on a similar report. I've tried using multiple Iif statements:

Group: IIf([UnitName]="Unit A","Group A", Iif([UnitName]="Unit B","Group A", Iif([UnitName]="Unit C","Group B"...etc

The problem is, there are about 60 units to be sorted into 25 or so groups. This makes for a very long IIf statement which I would try to write, but it ends up being too long to insert into the query (I get an error). I'm sure there's a better way...what are my options? Thanks.

 

[CJ_London]

have a group table something like

unitname...groupname
unit A........Group A
unit B........Group A
etc

and join your query to it on unitname

incidentally, group is a reserved word, using it as a field name is not a good idea - you can/will get errors at some point - particularly as you want to group this query

 

[hiwelcome]

Well, that was ridiculously easy. Thank you.