Query to Add field to Identify list of items

[brianb]

I'm not sure exaclty how to explain what I'm wanting but here goes;



I have a query that is pulling about a couple thousand rows of data w/about 25 columns. One of the cols has an ID tag. The id is a abbreviated name. There are about 30 different IDs. These IDs can be grouped in to 4 differnet main groups. So for example IDs ABC, MNO, QR would be group 1, IDs XYZ, HIJ, can be group two.

What I want to do is add a field into my select query and label it something like Grouping, and if id is ABC, MNO, QR, then the field would show Group1, if ID is XYZ, HIJ, field woudl show Group2, now I know i can do this with an IIF statment, but w/about 30 different IDs that would make it pretty long. Any way to do this in a shorter simpler way? I have som basic vba knowledge, but nothing to code this. Thanks

[pbaldy]

The normalized approach would likely be a table with the two fields:

ABC...1
MNO...1
XYZ...2

That is more maintainable than an IIf(), and in your query you would simply join in this new table.