"crosstab query"

[Mike]

I have put together a database of all projects for my company. I have been
attempting to creat a report that will show a table using the following:
Budget Line for the rows, Fiscal Year for the Columns and the value will be
the total cost of all projects that fall in the budget line and fiscal year.
I have been unsuccessful to this point. What should I do? I also need the
table to show all 0 values for example if no work was done under a certain
budget line in a certain year.

 

[Allen Browne]

"Fiscal year" means different things in different parts of the year, but use
DateAdd() to move the months into the year you need. For example, if your
fiscal year begins on July 1, type something like this into the Field row of
your query:
TheYear: DateAdd("m", -6 [Budget Date])
That field becomes the Column Heading of the crosstab.

The Budget Line field is the Row Heading of course.

And you probably need Sum in the total row under the amount field, which
becomes the Value for the crosstab.

Once you have that working, you can add Nz() to the query statement to get
it to replace the nulls with zero. Switch to SQL View (View menu, in query
design.) The first line will say something like this:
TRANSFORM Sum(Table1.Amount) AS SumOfAmount
Add the Nz() so it becomes:
TRANSFORM Nz(Sum(Table1.Amount),0) AS SumOfAmount

Access sometimes gets confused about the data type when you introduce Nz(),
so it would be worth adding CCur() or CDbl() or CLng() or whatever around
the result, like this:
TRANSFORM CCur(Nz(Sum(Table1.Amount),0)) AS SumOfAmount