Multiple Column Report - esp for Duane Hookom

[Guest]

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.

 

[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.

 

[Guest]

WOW - your response was 19 minutes after I submitted my question. Do you get
notified whenever someone enters you name in a posting?

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.

Indeed - the page break is tied to [OrgID].

The two tables I described are related by AcctNo - the Primary Key of
TYa1_ChartofAccts, and a foreign key in TAa1_AllAccts. It is a one-to-many
relationship.

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.

Remember that the account values in [TAa1_AllAccts] are in fact already
annual totals of accounts for these water districts taken from their year end
audited financial reports. For example a record in the table is:

OrgID: 2078
FY: 2003
AcctNo: 4600
AcctValue: 547,945

By relating [OrgID] and [AcctNo] to other tables to get their related
values, this record indicates that ($)547,945 is the annual total of all
Residential Water Sales by Millview Water District in Fiscal Year 2003.

I'm not sure, but I think this structure precludes using a totals query that
would add all of Millview's Residential sales together because the number is
already an annual sum.

Also - in the current structure there is no field named "residential".
"Residential" is a field value in [TYa1_ChartofAccts]. That table's two
fields are [AcctNo] and {AcctName]. So the record with "AcctNo = 4600" has
"AcctName = Residential" as its mate.

I spent some time just now describing the actual queries I used up to this
point, but it became sort of long. I appreciate your time and don't want to
waste it. Tell me if you want to see any or all of those. There are 4 sets of
queries.

The first two sets of queries combine accounts into one value for the Income
Statement. 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).

The 3rd query selects all the accounts that are not affected in the 2 query
sets above.

The final query combines the results of these 3 queries to produce the
dataset I've been using in my attempts to create the IS report.

Do you want to see any or all of those? (Sort of long as I say.)

I appreciate the help Duane.

John D

 

[Duane Hookom]

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?

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?

--
Duane Hookom
MS Access MVP

WOW - your response was 19 minutes after I submitted my question. Do you
get
notified whenever someone enters you name in a posting?

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.

Indeed - the page break is tied to [OrgID].

The two tables I described are related by AcctNo - the Primary Key of
TYa1_ChartofAccts, and a foreign key in TAa1_AllAccts. It is a one-to-many
relationship.

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.

Remember that the account values in [TAa1_AllAccts] are in fact already
annual totals of accounts for these water districts taken from their year
end
audited financial reports. For example a record in the table is:

OrgID: 2078
FY: 2003
AcctNo: 4600
AcctValue: 547,945

By relating [OrgID] and [AcctNo] to other tables to get their related
values, this record indicates that ($)547,945 is the annual total of all
Residential Water Sales by Millview Water District in Fiscal Year 2003.

I'm not sure, but I think this structure precludes using a totals query
that
would add all of Millview's Residential sales together because the number
is
already an annual sum.

Also - in the current structure there is no field named "residential".
"Residential" is a field value in [TYa1_ChartofAccts]. That table's two
fields are [AcctNo] and {AcctName]. So the record with "AcctNo = 4600" has
"AcctName = Residential" as its mate.

I spent some time just now describing the actual queries I used up to this
point, but it became sort of long. I appreciate your time and don't want
to
waste it. Tell me if you want to see any or all of those. There are 4 sets
of
queries.

The first two sets of queries combine accounts into one value for the
Income
Statement. 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).

The 3rd query selects all the accounts that are not affected in the 2
query
sets above.

The final query combines the results of these 3 queries to produce the
dataset I've been using in my attempts to create the IS report.

Do you want to see any or all of those? (Sort of long as I say.)

I appreciate the help Duane.

John D

 

[Guest]

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:

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/com...8ada&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 Sheet 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

 

[Guest]

Duane

One step forward, one step back -

1) Re data structure, I realized given the nature of my data I can "have my
cake and eat it too".

The DB contains historical financial statements for Special Districts, and
in the 5 years I've paid attention the State Controller has never changed the
data after its annual report.

So - without describing how (not important I think), I now have both the
470K record [TAa1-AllAccts] with its related [TYa1_ChartofAccts] table, AND a
[WaterIS - Residential; Irrigation; etc.] table that has a separate field for
each row in the Water District Income Statement. I realize this is redundant
and is probably not good practice generally, but in this specific case with
extremely stable data it seems OK to me - each data structure supports
different purposes.

So - having built the [WaterIS - Residential; Irrigation; etc.] table, I
used it in the KB article's procedure and GLORY BE I now have most of what I
wanted - the first column has account names, the 2nd through 4th columns have
values for the past 3 fiscal years.

That's the step "forward" (I think). Here's the step back.


2) Here's how I'd like the report to look:


Revenues and Expenses
Name of Special District
Name of County

2005 2004 2003

Residential $20,000 $19,000 $18,000
Irrigation 6,000 7,000 5,000
etc.

So - seems to me this is a subreport inside a report. I set up a report for
the "headers" - Title of Report, District Name, County Name. I then put the
KB-style multicolumn report in the detail section as a subreport.

The problems now are:

a) only the column of account names "prints";
b) a second page of account names prints next if there is a 2nd FY record,
and a 3rd page of account names prints if there is a 3rd FY record. However,
the "headers"

S0 - I think relevant questions are:

A) Can a KB-procedure multicolumn subreport be embedded in a master report
in this general layout?

B) If so, what would you need to know to see what I'm "doing wrong"?

Thanks

John D

 

[Duane Hookom]

I'm not sure why you even need the kb solution since you already have "the
first column has account names, the 2nd through 4th columns have
values for the past 3 fiscal years."

--
Duane Hookom
MS Access MVP

Duane

One step forward, one step back -

1) Re data structure, I realized given the nature of my data I can "have
my
cake and eat it too".

The DB contains historical financial statements for Special Districts, and
in the 5 years I've paid attention the State Controller has never changed
the
data after its annual report.

So - without describing how (not important I think), I now have both the
470K record [TAa1-AllAccts] with its related [TYa1_ChartofAccts] table,
AND a
[WaterIS - Residential; Irrigation; etc.] table that has a separate field
for
each row in the Water District Income Statement. I realize this is
redundant
and is probably not good practice generally, but in this specific case
with
extremely stable data it seems OK to me - each data structure supports
different purposes.

So - having built the [WaterIS - Residential; Irrigation; etc.] table, I
used it in the KB article's procedure and GLORY BE I now have most of what
I
wanted - the first column has account names, the 2nd through 4th columns
have
values for the past 3 fiscal years.

That's the step "forward" (I think). Here's the step back.


2) Here's how I'd like the report to look:


Revenues and Expenses
Name of Special District
Name of County

2005 2004 2003

Residential $20,000 $19,000 $18,000
Irrigation 6,000 7,000 5,000
etc.

So - seems to me this is a subreport inside a report. I set up a report
for
the "headers" - Title of Report, District Name, County Name. I then put
the
KB-style multicolumn report in the detail section as a subreport.

The problems now are:

a) only the column of account names "prints";
b) a second page of account names prints next if there is a 2nd FY record,
and a 3rd page of account names prints if there is a 3rd FY record.
However,
the "headers"

S0 - I think relevant questions are:

A) Can a KB-procedure multicolumn subreport be embedded in a master report
in this general layout?

B) If so, what would you need to know to see what I'm "doing wrong"?

Thanks

John D

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?

 

[Guest]

I'm not sure why you even need the kb solution since you already have "the
first column has account names, the 2nd through 4th columns have
values for the past 3 fiscal years."

Duane -

But I used the Knowledge Base (KB) article solution to achieve those
results. I adapted their instructions to my situation and - with some
tweaking - it worked.

The issue now is how to get the "Title" "headers" at the top of the page -
and MAYBE footnotes at the bottom.

The KB solution requires you to set the width of the report relatively
narrow. That's so that when the "event procedure" executes, the column you
set up in Design will repeat across the page as many times as it needs to.
That's how I'm getting the rows and columns of the Income Statements I want.
BUT - because I need to set the width very narrow - 1.5" in my case - a
Section Header is also limited to 1.5" - and won't print in the middle of the
full page (I think).

The issue (I think) is how to embed the KB procedure subreport into a larger
report whose sole purpose is to have the Titles centered on the printed page:

Revenues and Expenses
Name of Special District
Name of County

As I say - when I try to do so, only the "row titles" column prints - none
of the data columns do. AND, I get another page of row titles for each fiscal
year of data for each District.

That's my delimma.

Thanks for your help Duane.

John D

 

[Duane Hookom]

Are the labels you want to display on the left actually column titles from
your report's record source? From what you stated I expect the labels you
wanted to see were account numbers which were data values from an
AccountNumber column.