CrossTab with Defined Columns

[esipp]

I am trying to create a CrossTab query from a table of expenses:

tblExpenses:
PropertyID
ExpenseType
Amount

There are many kinds of Expense Types. I want to use ExpenseType as the column header, BUT I want there to be 3 categories "Rent Expense," "Taxes," and "Other."

HOW do I group all expense types <> "Rent Expense" and "Taxes" in the third column?

I can get a comprehensive query with many columns, or set criteria to get just "Rent Expense" and "Taxes" columns, but I cannot get the 3 columns.

How can I do this? Thanks for your help!!

 

[gromit]

Hi -

I would first create an intermediate query to just sort the expenses into the appropriate groups. E.g. create a field in the query ExpenseType2 and use IIF to create the types of Rent Expense, Taxes and Other.

You may be able to just use the crosstab directly off of this intermediate query, or you may need to include an IN clause in the crosstab to force all three columns to appear.

hth,

- g