Normalizing Budget Data (can't figure out how to start!)

[MorningSun]

Okay, I am the queen of non-normalization so before I begin this next database I'd love some suggestions on design so that I don't have to come back here and admit that I made a stupid mistake in the beginning!

I would think this would be simple but am having a really hard time...

I need a budgeting database. Currently our managers are keeping track of Actual Expenditures and Budgeted Amounts on an Excel spreadsheet with formulas to calculate totals etc. What I have been asked to do is make a database out of it.

The spreadsheet is as follows:
Jan | Feb | Mar | Apr...
Salaries
----------------------------------------------------------
Overtime
----------------------------------------------------------
Salaries for STD
----------------------------------------------------------
Total Salaries
----------------------------------------------------------
MICP
----------------------------------------------------------
US FICA
----------------------------------------------------------
Group Insurance
----------------------------------------------------------
Recognition
----------------------------------------------------------
Total for Personnel
Etc...

There is a section for Actual Expenditures as well as what is being planned for the next year. Both are exactly the same.

I am trying to figure out how to normalize but can't seem to figure out how I get an amount for each item for each month into a table without repeating stuff, ie SalariesJan, SalariesFeb, SalariesMar etc... for each item to be budgeted. If I go the opposite way, I'll have JanSalary, JanOvertime, etc.. and there are too many items to do this. I need for it to look like the spreadsheet (so they can see a whole year's worth Jan-Dec of each item to budget for)Actual vs Planned could be on different forms or subforms but I need Actual to show at once and Planned to show at once.

Am I making any sense at all?

Any suggestions as to how to get started would be greatly appreciated. I don't want to get myself into another normalization mess but I just can seem to get it to make sense!

THANKS SO MUCH

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[SQLSister]

Your main Expenditures table will consists of
Amount, Department, category, date. And some sort of ID field.

You can either then have a budgeted table with the same fiedls or add a field to the first table to tell you whether the record is a budget or expenditure.(YOu won;t want to call the table Expenditures in that case)

I would have lookup tables for the department names and one for the categories. Use these on the data entry form to limit them to the existing categories and departments. Frankly I would denormalize here and store the actual name rather than a key field.

Then to get the data in a report, create a cross tab query.

I hesitate to ask, but surely this information is already avaliable in your accounting software? Wouldn't it be more efficent to write a query against it instead?



Questions about posting. See faq183-874

 

[MorningSun]

I know nothing of the Accounting Software here. My bosses ask me to do something and I do it. This is outside of the big picture and is being used departmentally prior to being submitted on the corporate level.

I still don't think that this solution gives me the ability to create a form where the users could see all budget items and fill in amounts for all months in the year. One record could have Amount, Department, Category, date and ID field but how would I get 12 different dates entered for each category from one form so that the user can see everything they are entering at once?

I may be at a loss here. Just thinking Access is much better than Excel but just can't figure out how to get Access to work like Excel!

Thanks so much!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[1oldfoxman]

I'm no Access guru (in FoxPro you'd accomplish your goal with a Grid Control Object, but I don't see any reference to this in the Access documentation). Looking at the online help in Access ('97 in my case) under:

Subforms: What they are and how they work

With two subforms nested by either:

Dept
Period
Category

Or:

Period
Dept
Category

you should be able to present the data you're after for review or entry. Intuition and experience tell me that keeping the budget (planned) and actual expenditures in separate tables will be best, but it really comes down to exactly how you will be maintaining, querying, and using the data overall.

SQLSister's advice seems reasonable. It sounds like you're anticipating users entering their own data records (including creating them) so I would add a lookup table for Period to enforce consistent data in that field (Jan, Feb, etc or even Jan04, Jan05, Feb04, Feb05, etc).

Probably more good advice will come from others as well.......

hth,
Dennis

 

[RiverGuy]

The truly normalized way would be to take Dennis's suggestion. This requires the user's to have an understanding that they need to fill out line items, and "pick" a month for each line item. Therefore, they would type in the amount, pick the department, and pick the month.

But I think part of the problem is that you wish it to appear de-normalized to your users. The way it seems you want to run it is to bring up a form for a year, and have all your deparments with twelve months listed for each.

For starters, you may consider making the Month and Deparment the combined primary key. This way, you cannot get two May records for the same department.

Furthermore, to display this to the user in the format you want, will require some VBA coding and a custom-type form.

 

[lespaul]

can't figure out how to get Access to work like Excel

that's because access WON'T work like excel!

Check out 'The Fundamentals of Relational Database Design' for some ideas on normalization.

At this point I wouldn't worry too much about how the users will enter the data. If you have a normalized structure, then you can design just about any type of UI that will do what you want.

HTH

Leslie