If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
"Deconstruct" a large financial report table
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 structu 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 |
#2
|
|||
|
|||
"Deconstruct" a large financial report table
On Thu, 28 Sep 2006 14:00:01 -0700, John D
wrote: 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 structu 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] |
#3
|
|||
|
|||
"Deconstruct" a large financial report table
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 ( http://www.dbforums.com/archive/inde...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 |
#4
|
|||
|
|||
"Deconstruct" a large financial report table
On Fri, 29 Sep 2006 12:38:02 -0700, John D
wrote: 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 ( http://www.dbforums.com/archive/inde...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] |
#5
|
|||
|
|||
"Deconstruct" a large financial report table
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 |
#6
|
|||
|
|||
"Deconstruct" a large financial report table
On Fri, 29 Sep 2006 16:41:02 -0700, John D
wrote: 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] |
#7
|
|||
|
|||
"Deconstruct" a large financial report table
YEAH!!! It works.
Thanks so much for your help John. John D |
Thread Tools | |
Display Modes | |
|
|