Grouping dates to months and display results as columns in a report 1

[jckokko]

I have a query which returns expense date (ExpDate).
I would like to group the ExpDate by months: 2004/01, 2004/02, 2004/03,...,2004/12. Then I want to display the months as columns. But I can not think of a way how to do it. Help.
Below is the query statement that selects departments and their expense categories.
----------
SELECT tblDepartment.Name AS DepartmentName, tblExpense.Amnt AS Amount, tblExpense.ExpDate, tblExpense.ExpCategId, tblExpenseCategory.Category, tblBudgetThisYearDepartment.BudDepAmnt AS DepartmentBudgetAmnt
FROM (((tblExpense INNER JOIN tblDepartment ON tblExpense.DepId = tblDepartment.DepId) INNER JOIN tblTransaction ON tblExpense.TransId = tblTransaction.TransId) INNER JOIN tblExpenseCategory ON tblExpense.ExpCategId = tblExpenseCategory.ExpCategId) INNER JOIN tblBudgetThisYearDepartment ON tblDepartment.DepId = tblBudgetThisYearDepartment.BudDepId;
-----------

 

[dhookom]

You can use a crosstab query with Format(ExpDate,"yyyy/mm") as the column heading.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[jckokko]

Thanks Duane. It works.