Combine certain records when calculating averages

[Plasmodicum]

I have two fields of interest for a query, Field1 and Field2. Field1 is qualitative (text) and it may contain either a 1-digit number, a 2-digit number, a number and a letter, or just a letter. Field2 is quantitative data (integers). I am using the query to find the averages in Field2 as grouped by Field1 (identical Field1 values). However, there are certain groups in Field1 which, for this query, I would like to treat as one group. They all follow a similar, unique format for Field1: "0[A-G]", e.g. 0A, 0C, etc. Ideally, the query would rename/collapse/combine all these as "Unk" and provide their combined Field2 averages (and combined counts).

Now, for sorting purposes, I have already coerced the single digit numbers in Field1 to 2-digit numbers with the following:

Expr1: IIf(IsNumeric([Field1]), Format([Field1],"00"), [Field1])

This allows all numbers to be in numerical order, followed by letters in alphabetical, which is desirable. So, if your solution can work around or with this already in place, that would be excellent.

This seems like a simple problem, but I am brand new to Access.

[June7]

Need to calculate a value for this group to use for sorting/grouping by this other rule. What exactly is this rule? All values with a letter A-G? Any letters after G?

IIf([Field1] Like "*[A-G]*", "Unk", IIf(IsNumeric([Field1]), Format([Field1],"00"), [Field1]))

or

IIf(IsNumeric([Field1]), Format([Field1],"00"), "Unk")

[Plasmodicum]

June7,

Nailed it. The nested IIf function worked exactly right. I knew it would seem obvious afterward. Thank you so much.

Answer: IIf([Field1] Like "0[A-G]", "Unk", IIf(IsNumeric([Field1]), Format([Field1],"00"), [Field1]))