Multiple Column Report - esp for Duane Hookom
- From: John D <JohnD@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 19 Oct 2006 12:11:02 -0700
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 spreadsheet.
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.
.
- Follow-Ups:
- Re: Multiple Column Report - esp for Duane Hookom
- From: Duane Hookom
- Re: Multiple Column Report - esp for Duane Hookom
- Prev by Date: Re: what is done withe the report wizard's results ?
- Next by Date: Create multiple reports using Filter and save as RTF
- Previous by thread: Re: what is done withe the report wizard's results ?
- Next by thread: Re: Multiple Column Report - esp for Duane Hookom
- Index(es):
Relevant Pages
|
