Genisis of a Budget area

[Guest]

Here is the beginning of what I am working with:

3 main budgets broken down as:
1 main budget = 5 sub budgets
1 main budget = 4 sub budgets
1 main budget = 18 sub budgets

Main budget as the base which the sub budgets will deduct from, each sub
budget will have it's own budget which will be broken into 5 quarterly
reporting periods, a cumulative YTD and an ending balance or remaining funds.

Each budget has 3 columns, budget, Parent share, Child chare. budget has 33
fields, parent has 36 (3 are %) and child the same as parent. The fields are
all identical in their field names.

Eventually I had calculated a total of 187 fields that are all the same just
differing in their quarters and sub budget designation.

I was wondering if with the complexity and to make it easier for the person
entering the data. Can I create one main form that would be driven by sub
forms in a drop menu? I.E. Either 1 drop down that has all the sub budget
names, then another dropdown that has the Quarter period?

Each will have to be compiled into individual report, cumulative report of
all subs then one cumulative report summing YTD and remaining from the parent
budget.

I couldn't find any examples of a form that I could get an idea from. most
of the budget forms on Microsoft are household or not represented by multiple
accounts or quarters. Eventually this is going to grow and aquire more
"children" which will need to be reported on. Building it right is my first
concern, the reports will come later and are easier to deal with.

Thanks for any input, I know this is long. I am trying to keep the
programming side as easy as the user side.

 

[John Vinson]

Here is the beginning of what I am working with:

3 main budgets broken down as:
1 main budget = 5 sub budgets
1 main budget = 4 sub budgets
1 main budget = 18 sub budgets

Main budget as the base which the sub budgets will deduct from, each sub
budget will have it's own budget which will be broken into 5 quarterly
reporting periods, a cumulative YTD and an ending balance or remaining funds.

Each budget has 3 columns, budget, Parent share, Child chare. budget has 33
fields, parent has 36 (3 are %) and child the same as parent. The fields are
all identical in their field names.

WHOA. Your table structure IS WRONG. Storing data - periods, quarters,
parents and children - in one record might make sense for a
spreadsheet, but it is simply incorrect for a normalized table. I'd
suggest ONE budget table:

Budgets
BudgetID Primary Key <maybe an autonumber if no natural key>
BudgetName
BudgetDateStart
BudgetDateEnd
Amount
ParentShare
ChildShare

Eventually I had calculated a total of 187 fields that are all the same just
differing in their quarters and sub budget designation.

I was wondering if with the complexity and to make it easier for the person
entering the data. Can I create one main form that would be driven by sub
forms in a drop menu? I.E. Either 1 drop down that has all the sub budget
names, then another dropdown that has the Quarter period?

With the normalized structure above, this is very easy. MUCH harder
with your wide-flat design.

Each will have to be compiled into individual report, cumulative report of
all subs then one cumulative report summing YTD and remaining from the parent
budget.

DON'T confuse data *storage* with data *presentation*. You should not
have any YTD, quarter-to-date, sums, or other calculated fields in
your table AT ALL. Calculate them in the query instead.

I couldn't find any examples of a form that I could get an idea from. most
of the budget forms on Microsoft are household or not represented by multiple
accounts or quarters. Eventually this is going to grow and aquire more
"children" which will need to be reported on. Building it right is my first
concern, the reports will come later and are easier to deal with.

All the more reason to use an expandable normalized structure rather
than increasing the width of the table!

Thanks for any input, I know this is long. I am trying to keep the
programming side as easy as the user side.

Normalized data structures are ESSENTIAL to do so.

John W. Vinson[MVP]

 

[Guest]

John,

Thank you for a direction, I knew I was opening Pandora's box and looking at
this too hard and letting it overwhelm me. True my key is to simplify it, I
didn't have anyone here to kick the idea or problem to so that I could hear
what I wanted to do. I had thought late last night to assign each parent a
code which will guide all the inforamation and it's area which should if I
query it right allow me to do minimal tables and forms. Doing all the
calculations via query and report output.

it will be another long day with this one, and thank you for giving me an
edge in direction.