Re: Multiple Column Report - esp for Duane Hookom

It's not real clear when you have field names and data samples but don't
state any relationship. For instance "page break and the Second Water
District" which should better be explained as page break between [OrgID] or
some other field. I don't know what field you are grabbing "Second Water
District" from.

Can you create a simple totals query that groups by district, field of
"WaterSales", Field of "residential" and displays field of "20,000" etc? If
so, reply back with the SQL view of this totals query.

--
Duane Hookom
MS Access MVP

"John D" <JohnD@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6AA2B27E-56BF-4F0E-9D84-0C9C5752FF7C@xxxxxxxxxxxxxxxx
I've read several other threads where Duane Hookom answered similar
questions
- hope he (you) sees this.

I'm developing a DB to contain historical financial statements for water
districts in our state. I want to print a report that looks like this
(sorry
for spacing weirdness):

FIRST WATER DISTRICT
2005 2004 2003 etc.
WaterSales:
Residential 20,000 18,000 17,000
Industrial 10,000 15,000 20,000
Irrigation 6,000 20,000 0
Etc
______ ______ ______
TTL OPER REV 100,000 85,000 120,000

(rest of IS)

Then a page break and the Second Water District, etc.

IE - just like a spread***.


My table that holds the actual report data is structured:

TAa1_AllAccts (table name):
OrgID (PK)
FY (PD)
AcctNo (PK)
AcctValue

This table holds only the year-end value of each account - the composite
Primary Key enforces this by allowing only one AcctValue for each "OrgID -
FY
- AcctNo".

Another table is the Chart of Accounts:

TYa1_ChartofAccts:
AcctNo (PK)
AcctName

AcctNo determines the order in which accounts are printed on the IS.

The values of several accounts in the Chart are added together and
reported
as one line in District Income Statements. Example, 5 separate Water
Services
Revenue accounts are added together and reported on one line titled "Water
Services" on the IS.

Therefore, I built a series of queries to combine specific account values,
leading essentially to a "restated" AllAccts dataset, except with the
combined accounts restated as a single row for each OrgID/FY combo. The
query
has these fields:

OrgID
FY
ISOrdr
OrgName
AcctName
AcctValue

I can't figure out how to use this data structure to create the report I
want.

M. Hookum on 3/1 referred Diana with a similar question to:

"Try this Knowledgebase article on multiple columns with labels on the
left
http://support.microsoft.com/default.aspx?scid=kb;en-us;210044&Product=acc.";

The problem is this solution assumes each row to be printed is a different
field with its own field name and value. My Account Values table would
have
to look like:

OrgID (PK)
FY (PD)
Residential
Industrial
Irrigation
And so on for every line in the Income Statement.

On the face of it, such a table seems to not be a "well-normalized" data
structure. But it would work for the solution to my problem defined by the
Knowledge Base article.

Help?

I appreciate any help. Thanks: John D

PS - the Knowledge Base article says Access doesn't have a report option
to
easily produce such a report. Why, for heaven's sake, doesn't it? This
kind
of report is widely used - seems Access should make it easy to produce.





.