Solved: Access/Excel Drill Down no Smart Tags

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

grnadpa

Thread Starter
Joined
Aug 31, 2009
Messages
14
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
 

grnadpa

Thread Starter
Joined
Aug 31, 2009
Messages
14
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

Joined
Mar 8, 2005
Messages
19,895
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

Thread Starter
Joined
Aug 31, 2009
Messages
14
Thank you. Though I think I understand, an example would be terrific. Do you need my email address?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
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.
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top