Solved: Access/Excel Drill Down no Smart Tags

[grnadpa]

Good morning,

Looking for a strategy to produce a drill down capability in either Access 2003 or Excel 2003 that is limited to the standard installation (i.e. no add ons -- such as Smart Tags).

I recommended Microsoft Project, but the project sponsor is not open to this option.

Though the sponsor is still developing the specs, the data will arrive in a microsoft comma-delimited (.csv) format (yes .xml would be better) with at least 4 levels of hierarchy. Not all hierarchies will go that deep, others may go deeper. (i.e. some may be only 2 or three levels, others may go to 5 or "n" levels).

I'm willing to use pivot tables, but I don't believe they will nest.

I'm willing to use Access Forms, but I believe they only go down to one sub-form.

I have used VBA, but it's been awhile.

Any ideas?

Much appreciated: Grnadpa Brian

 

[OBP]

Access nests many subforms, not just one.
It would be ideal.

 

[grnadpa]

Access nests many subforms, not just one.
It would be ideal.

So can I use the same form as the parent and as the child?

That is ... Suppose I have two tables call them Task and Project. The Task holds the specific granular data for the task including, let's say, the number of person hours the task requires. It will also contain a foreign key to one record on the Project table in a many (Task) to one (Project).

The Project record contains only its autonumber key, a description such as "technical effort" and a foreign key to another record on THE SAME PROJECT table whose description might be "IT Dept Effort" with a one to many relationship to records in its own table.

This "IT Dept Effort" record, in turn, will point to another Project record entitled, say, "Phase One" which in turn Points to a Project record entitled "Project DrillDown".

Except for the task form, all the other hierarchies could be the same form layout -- specifically, the description, and a list of the names and keys of its children and the sum of the hours of all the task records attached to the chain. I will not know in advance how many levels each drill down will contain.

 

[OBP]

Yes, you can use the same Form Structure, but with different names and each with it's unique recordset (Query).
I would suggest a Related "Sub" Table for each layer data linked either to the ProjectID or to the previous layer.
I can show you some "Project" type databases if you want.

 

[grnadpa]

Thank you. Though I think I understand, an example would be terrific. Do you need my email address?

 

[OBP]

I have private mailed you my email address, when you contact me I can send you a couple of databases.
I can also advise you on what you want to do.
If you find it is what you need then perhaps you can mark the Thread as Solved.