table design for budgets

[dbpcar]

i have a design problem-I am setting up a database to use for budgeting. One table (the income statement) will have historical data-each month's total in each account. The table will be updated once a month with the closing numbers.
The question-is it better to have the accounts as fields and the months as records or the other way around. This database will be the source for the historical analysis in pivot tables in excel.There are approx 1800 accts and 45 months of data.

 

[Maquis]

Hmmm, if it were me, I would design 2 tables.

The first table would contain accounts and all the fields pertaining to accounts would be stored here.

The 2nd table would contain only the info that changes from month to month. It would contain an account id which would link it back to the accounts table and a month field. The Primary key would be the account id and the month field combined. Other fields could contain monthly numbers.

You can then use queries to produce views in the format that Excel needs. Maq B-)
<insert witty signature here>

 

[THoey]

Actually, I wouldn't do it either way. There is always the chance of adding new accounts, etc... I would create a table with three fields:

Field Name Type Description

AccountID NUMBER Foreign Key to an Accounts table
IncomeMonth DATE I'd use the complete date (MM/DD/YYY)
IncomeAmount NUMBER Amount of income

You would need to have an accounts table that had an autonumber for a primary key. This would tie the accounts and their income together.

Hope that helps...

Terry M. Hoey

 

[THoey]

Maq, beat me to it... ;-) Terry M. Hoey

 

[dbpcar]

Thanks for the help. The three fields will work great with the format I have the data in now. dbpcar