Report based on Crosstab Query

  • Thread starter Thread starter Jan
  • Start date Start date
J

Jan

Each year for the past 4 to 5 years, I have used a small db (I created) to
supplement our accounting systems lack to provide an actual report for each
and every fund budget we have. The detail table in the db I created only has
3 columns: Fund, Acct, and Amount.

I created a crosstab query to list:
CatDesc, Acct, Importance, Name, and each fund as columns and the amount.

CatDesc Acct Imp Name 300 310 320 340

This portion works great except for the fact when I want to create the final
draft of the budget. I manually arrange the fund columns into the report.
How can I create a report to dynamically arrange the fund columns based on
the fund number? Is there such a way to do this without having to spend 15
minutes to add a column to the report? Any help will be greatly appreciated.
 
I'm not sure what takes so long. Aren't the fund columns fairly static from
the beginning to the end of the year?
 
The funds are static as long as they don't delete a fund - this could happen
if the funding is no longer supported via Grants or some other source or if
they add a fund (receive a new Grant or other source).

The background information for this report are:
Table: Fund; Table: Accounts; Table: Details
Funds change from year to year
Accounts are pretty static but each has a category associated, ie
Revenue = RE, Operating Expense = OE, Program Expense = PE, Other Expense =
OT and Salaries/Benefits = SB.
Details consists of Fund, Account, and Amount
Simple right?!

Well, to create the report, I have a select Query setup with:
Acct Category, CatDesc, Fund, Acct, and Amount

My Crosstab Query consists of two Queries: All Budgets and Accounts
Rows: CategoryDescription, Account, Importance, and Name
*Importance is a field to establish the rank of how each section should
appear, for example, Revenue is 1; Salary/Ben is 2, etc.*
Column Heading: Fund

We currently have 24 funds under Contract and about 20 under Revenue (two
different reports) for this year. Next year we could have 20 Contract funds
and 10 Revenue. It depends on the year and the source of funding.

Explanation of what's on the Crosstab Report:
Each CategoryDescription is a subReport and shows a total of each fund and
the percentage of that subReport. I.e. All Revenue accounts are reported in
its own section....All Salary/Benefits accounts are reported in its own
section...etc.

At the end of the report (in a nested subreport), I compare Revenue
(comprised of only Revenue Category) versus the Total Expenditures (the
remainder of Category Descriptions) plus the percentage comparison of each
fund against the Total Revenue or Expense.

The report footer of this subreport exists the final outcome of an amount
which shows a deficit or an excess.

So what takes so long is that I have to rename a total of 10 text boxes for
each new fund (7 details and 3 total boxes). Each year I have to recall this
process because it only occurs once and I would like to just create an
automated or dynamic report to handle it. I thought maybe, just maybe, I
didn't have to reinvent the wheel with this....I did find an Article titled
"How to create a dynamic crosstab report in Access 2002" but I'm trying to
fit my work into the idea...and I'm struggling a bit with it.

Is there any additional material that could help me?
 
Back
Top