"Deconstruct" a large financial report table

[John D]

I got an Access DB from our State Controller's Office (SCO) they use to
produce their "Annual Special Districts Report". SCO simply provides the data
in their annual report with very little analysis. I want to analyze districts
comparatively but the SCO table design makes it very difficult.

There are 16 tables in the SCO DB each of which contain data from one of
their specifics reports in their large "Annual Report" (Income Statement,
Long-Term Debt detail, Organizational information, etc.). Each table has one
record for each "Organization-Fiscal Year" (the Primary Key) with anywhere
from 10 to 150 other fields each of which is the annual value of an account
(or other type of data) reported on each specific report.

An example is the Water District Income Statement Table. The table's primary
key is two fields - OrgID and FY (Fiscal Year). Those are followed by about
100 fields each of which is the yearly reported value in an account - such as
WATSALE_RESIDENTIAL, WATSALE_BUSINESS, OPEXP_PUMPING, NON_OPREV_FEDERALGRANT,
NET_INCOME, etc.

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

The first 3 fields would a a combined Primary Key.

(I also think the "subtotal" and "total fields" such as NET_INCOME should be
calculated in queries rather than held as a field value in a table - but
that's a different issue.)

HERE'S THE PROBLEM: I haven't yet figured out how to break the big table
into this "one record for one account value" table.

I've "messed around" with a Make Table query. I'd think it should create a
new table for each account name (such as WATSALE_RESIDENTIAL) with a primary
key of "OrgID-FY-AcctNo". (I'd want to provide my own system of account
numbers.) But if I move the existing Primary Key to a new table with one
account value, I don't think I can send that same Primary Key to a second
table with a different account in that Make Table Query. If I could do that
I'd create, say, 100 new "single account" tables and then append them one to
another to create the large unified table. That would be a lot of work by
hand, but if I also have to create and run separate Make Table Queries for
each of the hundreds of separate accounts in the SCO DB - that's way too much
work it seems.

Can anyone point me in the direction I need to go?

Thanks in advance.

John D

 

[John Vinson]

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

VERY good thinking!

The first 3 fields would a a combined Primary Key.

(I also think the "subtotal" and "total fields" such as NET_INCOME should be
calculated in queries rather than held as a field value in a table - but
that's a different issue.)

HERE'S THE PROBLEM: I haven't yet figured out how to break the big table
into this "one record for one account value" table.

A "Normalizing Union Query" is the trick here. You certainly do NOT
want 100 different tables. You'ld populate this table using a query
like

SELECT OrgID, FY, "WATSALE_RESIDENTIAL" AS Account,
[WATSALE_RESIDENTIAL] AS Value
FROM wideflattable
WHERE [WATSALE_RESIDENTIAL] IS NOT NULL
UNION ALL
SELECT OrgID, FY, "WATSALE_COMMERCIAL", [WATSALE_COMMERCIAL]
FROM wideflattable
WHERE [WATSALE_COMMERCIAL] IS NOT NULL
UNION ALL
<etc etc>

You may need to do this in a couple of pieces, a UNION query with 100
selects may be too complex. Of course you can replace the
"WATSALE_RESIDENTIAL" with a number or a more readable account if you
wish.

You'ld then base a simple APPEND query on this UNION query and run it
to migrate the data into your new normalized table.

John W. Vinson[MVP]

 

[John D]

John - thanks so much - just the direction I needed.

Just spent most of the morning delving into UNION Queries as they apply to
my problem. May I ask two details questions?

1) You said - "A 'Normalizing Union Query' is the trick here." I Googled
that phrase and found a couple of direct string returns - one of which was
another posting by you ( www.dbforums.com/archive/index.php/t-329313.html). I
think "Normalizing" in this context means the EFFECT of applying a series of
proper UNION Queries in this situation rather than a specific structural TYPE
of UNION Query. Is that your meaning? (Put another way, I assume there is
only one TYPE of Union Query - although with lots of possible variations in
how it is expressed. However, you can use UNION Queries to achieve lots of
goals, one of which is to transform data structures into properly Normalized
Tables.) Just want to make sure I'm using the "Technical Language" correctly.

2) Does this procedure make sense to you? First, output the field names to
(say) an Excel spreadsheet. Second, create my system of account numbers using
that spreadsheet. (Ex - "WATSALE_RESIDENTIAL" could have an account number
next to it of, say, 4001. And so on.) Third, change all field names in Access
Table Design View to the new account number system. Fourth, do the UNION and
APPEND Queries you suggest to create a new normalized Table. Fifth, import
the Excel list as a new Table that I'd use in queries to associate the
account name with the account number.

Again - thanks for the help.

John D

 

[John Vinson]

John - thanks so much - just the direction I needed.

Just spent most of the morning delving into UNION Queries as they apply to
my problem. May I ask two details questions?

1) You said - "A 'Normalizing Union Query' is the trick here." I Googled
that phrase and found a couple of direct string returns - one of which was
another posting by you ( www.dbforums.com/archive/index.php/t-329313.html). I
think "Normalizing" in this context means the EFFECT of applying a series of
proper UNION Queries in this situation rather than a specific structural TYPE
of UNION Query. Is that your meaning?

exactly. Sorry for not being clearer.

2) Does this procedure make sense to you? First, output the field names to
(say) an Excel spreadsheet. Second, create my system of account numbers using
that spreadsheet. (Ex - "WATSALE_RESIDENTIAL" could have an account number
next to it of, say, 4001. And so on.) Third, change all field names in Access
Table Design View to the new account number system. Fourth, do the UNION and
APPEND Queries you suggest to create a new normalized Table. Fifth, import
the Excel list as a new Table that I'd use in queries to associate the
account name with the account number.

That would be one way; a bit simpler would be to build the table of
fieldnames and their corresponding accountnumbers, first, and use them
in the UNION query:

SELECT OrgID, FY, DLookUp("[AccountNo]", "[Accounts]",
"[AccountFieldname] = 'WATSALE_RESIDENTIAL'" AS AccountNo,
[WATSALE_RESIDENTIAL] AS Value
FROM wideflattable
WHERE [WATSALE_RESIDENTIAL] IS NOT NULL
UNION ALL...


John W. Vinson[MVP]

 

[John D]

John - oops. When I tried this Union Query:

SELECT OrgID, FY, "WATSALE_RESIDENTIAL" AS Account, [WATSALE_RESIDENTIAL] AS
Value
FROM TFia_SD_WATER_ENTERPRISE
WHERE [WATSALE_RESIDENTIAL] IS NOT NULL
UNION ALL
SELECT OrgID, FY, "WATSALE_BUSINESS", [WATSALE_BUSINESS]
FROM TFia_SD_WATER_ENTERPRISE
WHERE [WATSALE_BUSINESS] IS NOT NULL

It get the message" SELECT statement includes a reserved word or (mispelled
or missing) argument name, or punctuation is incorrect.

Can you see what's wrong?


The table's name is TFia_SD_WATER_ENTERPRISE because the SD_* is the
original name of the Table from SCO, and the TFia is my table naming
convention code to keep track of the dozens of tables I've got. (I'm building
this DB not just for Special Districts, but for every other type of local
jurisdiction in the state as well. So - in additioni to the 16 tables for
Districts, I've got another 45 to 50 or so for other types of organizations.

Thanks

John D

 

[John Vinson]

It get the message" SELECT statement includes a reserved word or (mispelled
or missing) argument name, or punctuation is incorrect.

Can you see what's wrong?

Probably the fieldname VALUE is a reserved word. Change AS VALUE to AS
<some other fieldname>.

John W. Vinson[MVP]

 

[John D]

YEAH!!! It works.

Thanks so much for your help John.

John D