Hierarchical data in Access or Excel (and how)? For the social science guy

[taidan]

Does it make sense to try to store hierarchical data for a model in Excel, or do i Have to use access (which is scary and intimidating ).

The catch is, not all of the parent categories have the same level of depth and not everything gets numerical values.

What program should I use and how would I do this?

Some of the data rolls up to the parent category and then goes into the model , and some gets plugged in directly.

Please see attatched pic for structure (absent the numbers).

Thanks!!!!
[imgnothumb]http://bytes.com/attachments/attachment/5785d1323371492/data-hierarchy.jpg[/imgnothumb]

[Rabbit]

What is it that you're trying to do with this data?

[taidan]

Hi Rabbi. Thanks for your reply.

Data agregation, modeling, storage and mathematical manioulation.

[Rabbit]

Then you should most likely use access over excel. There's no good way to lay out relational data in Excel. Not that it can't be done but it would be exceedingly difficult. If you don't know how many levels of hierarchy you're going to have, you can implement a tree into your tables and that will allow you to have as many levels as you need.

[taidan]

Great, thanks for your reply.

I have ZERO experience with Access. Any tools or sites you recommend to help me get started?

Also, the GUI is important. Any tools you can suggest to give gauges and other meters which move as the underlying data points change?

[Rabbit]

You may want to create a new thread asking for resources. A friend of mine taught me how to use it a while back so I don't really have a good resource for Access.

Just be aware that this sounds like a very complex project you're about to undertake and the road will be arduous. I'm not trying to discourage you but you should be prepared to put in the time and effort required to see this through.

The first step will be to learn the basics. And that may be the hardest step of all. It may be tempting to jump right into the project but I guarantee that if you do, you will end up redesigning multiple times over.

Once you're comfortable with database design and normalization, you can then begin on your project. You'll want to come up with a good design for the data to make sure it's adaptable and will fit your needs before you start creating your GUI. You don't want to have to redesign your GUI after realizing the data structure you've chosen is flawed.

Just keep in mind that we won't be able to do large parts of the project for you. But we can help you along with any specific issues that might arise along the way.

[NeoPa]

Some resources that may help. The first is critical to anyone working with Access, the others are in no particular order and are for you to research through. Rabbit's explanation is very good - both technically as well as what you can and cannot expert from experts here :

• Database Normalisation and Table structures.
• Microsoft Access Tutorial.
• Microsoft Access Tutorials (Strive4Peace).
• Microsoft Office Tutorials.
• VBA Tutorial (Excel).