Search code examples
ms-accesscrosstab

How can I group two rows in a cross tab query in MS - Access?

I currently have crosstab query in MS Access that groups data by the field "Type". This field is the row header in my crosstab query.

The 3 "Type" values are:

Equipment, Optional, Parts

I would like to combine the values of the Optional and Parts rows into one row (called Together) and present the crosstab query with just two rows:

Equipment

Together

How can this be done? Basically how can I group two rows in a cross tab query?

Solution

  • For the column displaying your Type field, use an iif expression to output Together if the value of the field is either Optional or Parts, else output the value of the field e.g.:

    NewType: iif(YourTable.Type in ('Optional','Parts'),'Together',YourTable.Type)

    Alternatively:

    NewType: iif(YourTable.Type='Optional' or YourTable.Type='Parts','Together',YourTable.Type)