Re: Multiple Column Report - esp for Duane Hookom

Duane - I've been working on this this morning - but my wife and I are going
to take a 2 day drive up to the Sierra Nevada to look at fall colors - and
she has politely informed me ; ) it's time to go. I'll get back to this
Sunday.

Answers to your two questions:

"Duane Hookom" wrote:

I new that Residential was a value in a field but you hadn't told us before
which field.
You stated
"Accounts 4610 through 4619 are Water Services,
which are reported as one line (Water Services) in
the State Controller's Income Statements.
4605 and 4609 are also reported together on one
line (Other Sales)."
Do you have a field in TYa1_ChartofAccts that suggests this grouping? If
not, why?

SHORT ANSWER - yes. If you'd like to know more I'll post explanation.


How close do you get if you use OrgID and AcctNo as the Row Headings, FY as
the Column Heading, and Sum of AcctValue as the Value?

I've been "playing" with several approaches.

1) PIVOT TABLE: Gets very close to what I want by using OrgName and ISOrdr
(see below for detail about what ISOrdr is - but essentially it's a "standin"
for AcctNo) as Row Headings and FY as column headings. I don't need to
specify a "Sum of AcctValue" as Value because the AcctValue is already the
annual total for that account - it's already in essense a "sum".

I wish I knew a way to somehow take the Pivot Table data/format and work it
up into the printed report format I want. It doesn't look like a "normal"
Income Statement - and for my users it needs to. I'll dig more into this when
I return - unless you tell me to not waste my time.


2) ACC2000: How to Print Labels on the Left Margin of a Report: This is the
Knowledge Base article to which you refered Diana. As I said before, I think
this procedure only works if each account - or row in the IS - is its own
field with a field name or Caption such as "Residential". Here's an
interesting rub:

The State Controller (SCO) produces Annual Financial Reports for all Special
Districts including Water. They use an older version of Access. Each type of
District (Water, Airport, Hospital, Non-Enterprise, etc.) has its own form of
report, and its own table, such as [SD_WATER_ENTERPRISE]. In these original
SCO tables each account has its own field - so the structure is:

OrgID (PK)
FY (PK)
OPREV_WATSALE_RESIDENTIAL
OPREV_WATSALE_BUSINESS
OPREV_WATSALE_INDUSTRIAL
....
OPREV_TOT (Note the table holds calculated values as well as independent
values for individual accounts.)

Some time ago I tentatively concluded that this data structure violated all
sorts of normalization rules. I submitted a question to the Design part of
this forum on 9/29 titled " 'Deconstruct' a large financial report table". (I
think this is a link to that posting:


http://www.microsoft.com/office/community/en-us/default.mspx?query=financial+tables&dg=microsoft.public.access.tablesdbdesign&cat=en-us-office-access&lang=en&cr=US&pt=327f343d-f0d6-4e07-aa53-9656edb98ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

YIKES!!!)

MVP John Vinson was kind enough to help me on my issues, and replied:

(My words) >I believe having each account's yearly value in one record for
every
"Organization - Fiscal Year" makes all sorts of things very difficult to do.
What I'd like to do - I think - is "deconstruct" the large Water District
Income Statement table into a table with the structure:

OrgID
FY
AccountNo
Value

(MVP Vinson's reply) VERY good thinking!

After considerable help from John V I accomplished the conversion of ALL 8
large Income State SCO tables into one large "TAa1_AllAccts" table with all
account balances for all Districts for the past 3 FY. 467,000 records!!!

But when I got to trying to replicate the form of the SCO reports - the
subject of my current postings - I hit the problem posed by this
KnowledgeBase article - "Microsoft Access does not have a report option that
enables the printing of labels only along the left margin of a report." But
the article provides a way to do this IF the file is structured as the
original SCO Table - one field for each account.

Any rate - I just tried to use the original SCO [SD_WATER_ENTERPRISE] table
in the procedure described in the KB article. I didn't quite get there, but I
got close enough to believe I will be able to do so.

WHICH BRINGS ME TO A "MASTER QUESTION": Should I go back to the original SCO
Data Structure? Too bad - spent the better part of 3 weeks converting the old
system to my new single-table system believing it to be a more properly
"normalized" structure that would pay dividends in the future.

My basic skill-set is a business-financial analyst. I want to apply those
skills to the analysis of local government finances - and therefore intend to
"crunch" these numbers pretty hard (complex analysis). I thought - without
really knowing but sort of "on faith" - that the more "normalized"
[TAa1_AllAccts] structure would be superior to trying to analyze 8 different
Income Statement tables and 9 different Balance *** tables, which have all
sorts of problems like different account titles for what are really the same
account, etc.

But - if I use the original SCO design, I will be able to replicate their
reports in Access (might have something to do with why that's the SCO design
in the first place).

Any thoughts?


3) Using [TAa1_AllAccts]: I haven't gotten "as close" to what I want using
my new structure - I'll tell you about it Sunday (another somewhat more
urgent reminder from my wife!).

Sorry for so many words, and once again I really appreciate your help.

John D
.