Capabilities Of Access (Tree Diagrams?!)

[matthewnorton]

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" (see sample attached). As you can see, this is where one branch has in turn several branches and this cascades downwards to some base values.

On the diagram you can see that I have used 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 - I have only shown this for the English leagues. These leagues are all made up of teams, only following the Premiership league, you can see some of the teams in it (Arsenal, Chelsea...). 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) - this has been illustrated with only "Gerrard". 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 Madrid and the Spanish data.

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.

Any help and feedback on this would be much appreciated,

Thanking you,

Matt.

 

[Pat Hartman]

Your question is way too broad for any specific help. Start by defining your tables and post your structure for review. Be sure to do some reading on normalization and database design before you start. With a properly defined schema, nothing you are asking for will be difficult.

Access doesn't provide any graphical display tools but it does have a useful control that will display your data in a nice hierarchial view and that is the TreeView Control. The TreeView Control requires code to load it since it isn't bound so search for articles on it from the MS knowledge base and the MSDN library.

Welcome, and keep us posted.

 

[matthewnorton]

I have tried to normalise my tables as you have suggested.
I think I have all the right tables, but I am worried about what relationships should be formed and where referential integrity should be enforced.
Things that I wish to do with the data are to compare averages (it shows blank cells when I try this with a query), and to do calculations in a query based on data already there. I have tried to follow all the instructions that I have on doing this, but cannot find the same layout as illustrated in examples (i.e. "Field Title" cell in a design view - e.g. to input a calculation such as [BasicWages£Thousands]*52).

If anybody has any suggestions on improving my database, they will be muchly appreciated.

Thanks guys,

Matt.

 

[ByteMyzer]

The database sample you posted online has much room for normalization.

For starters, tables Bundesliga, Championship, La Liga Primera, Ligue 1, Premiership and Series A can all be consolidated into a single table you could call Teams. You can then create a one-to-many relationship between table Country/League, field LeagueID and table Teams, field LeagueID.

 

[matthewnorton]

Thanks for the suggestion.
I have now amended the tables to fit as you have said, I have also created TeamID so that a team name isn't replicated throughout the respective players tables, i.e. Premiership Players.
Following from the previous suggestion, would the next comment be to do the same with the players as I have done with the leagues and teams?
My only concern with this would be the difficulty in clearly seeing which players play for who - but if this will be the correct way of proceeding then I am game to do it.

Thanks again to you,

Matt.

 

[Pat Hartman]

You will ultimately end up with three tables in order to make the relationship between teams and players.

tblTeams:
TeamID (autonumber primary key)
TeamName
etc.

tblPlayers:
PlayerID (autonumber primary key)
FirstName
LastName
etc.

tblTeamRoster
TeamID (foreign key to tblTeams)
PlayerID (foreigh key to tblPlayers)
StartDate
EndDate
etc.

It is the team roster table that links the players to a particular team.

 

[matthewnorton]

Thanks a lot Pat (&others!),
The change in structure of my tables and the relationships suggested have made it so much easier to construct queries and analyse things.

I'm glad you guys are here to help people like me!

Thanks again people!