# Applying Tree Diagram Calculations to Access 2000

G

#### Guest

Hi,

I am part of a team working on part of an ongoing project that requires a
flexible and easy to update database from which to compile our data. The
general format that we have, is that of a "Tree Diagram". This is where one
branch has in turn several branches and this cascades downwards to some base
values.

Using the example of "Costs In Football". Firstly the tree is branched into
countries where football leagues exist (England, France etc...), each of
these countries then has a number of leagues (Premiership, Championship
etc..). These leagues are all made up of teams, such as (Arsenal, Chelsea,
Liverpool...). Using the Liverpool team, the team is then made up of players
such as Gerrard, Reina etc...
These players have a specific postion where they play (i.e. Goalkeeper,
defence, midfield, attack). The next set of branches examines the costs
associated with each player (current value, weekly wage...)
The weekly wage is then examined in more detail by what components make it
up and their respective costs. I have stopped the tree here and I am using
this last line as the base values.

Now, what we wish to do, is to be able to calculate easily (and
automatically) what costs are associated at each level of the tree diagram.
i.e. "How much money is spent on wages in all the clubs in the Premiership
league", or "Which country spends the most on Sponsorship Deals?"

The flexibility of the database must also be high. Each year the teams that
make up each league are changed - we need to be able to make these changes to
the database - and the automatically calculated values must change along with
the changes.
Also, if a club buys a player - i.e. Liverpool (England) buy Beckham (Real
Madrid, Spain) then the details for both Liverpool and consequently the
Premiership league will have to change, along with the details for Real

In short, additions of data, changes of data and deletions of data must
result in the database automatically changing the values involved in each
step of the tree. i.e the change made in the Players branch must cascade up
and down the branch so that the calculations at every level are recalculated.

Kind regards,

Matthew.

ps. my Access knowledge is limited as I have only just started using it.

A

#### aaron.kempf

1) dont store calculations
2) some of these hierarchies you're talking about would be a lot of fun
with analysis services
3) do you have some sort of template to start from?
4) when you say tree-- like costs aren't associated at each level; but
you can calculate at each level
5) this salary thing is going to be a child table; a one-many
relationship