Dynamic Data Structure

[David M C]

Currently I have tables for Job, Group, Location, Task. One Job has many
Groups, one Group has many Locations, one Location has many Tasks. This
enables any given Job to be broken down into more managable chunks. However,
some Jobs may require a more or less detailed breakdown. Is there any way to
have a dynamic data structure? Those of you that have used MS Project will
know what I'm trying to achieve.

Thanks

Dave

 

[Lynn Trapp]

David,
I'm not completely sure I understand what you want, but let me give a stab
at it. Do you need some jobs to have all levels and other jobs to have only
some. For instance, Job 1 might have multiple Groups, Locations, and Tasks,
while Job 2 might have no need for Groups or Locations and only Tasks? Is
this what you have in mind? If so, the simplest way to do that would
probably be to include a foreign key to all parent tables in each of the
child levels.

Jobs
JobID
Other fields specific to a job

Groups
GroupID
JobID -- foreign key to Jobs Table
Other fields specific to a Group

Locations
LocationID
GroupID -- foreign key to Groups Table
JobID -- foreign key to Jobs Table

Tasks
TaskID
LocationID - foreign key to Locations Table
GroupID - foreign key to Groups Table
JobID -- foreign key to Jobs Table

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[David M C]

You're almost there with what I want. Within the bounds of the already
defined structure, what you have suggested would work fine. However, I would
like something that would allow me to provide more and more "nesting". In MS
Project you are able to "indent" tasks under any number of "Summary" tasks.
For instance:

Job 417:
Group 2:
Big Street:
The Hall:
Plumbing
Wiring
Decorations
The Lodge:
Plumbing
Decorations

I would like to mimic this behaviour in Access. I know it can be done
because MS Project can be saved as a database and opened with Access.

Any ideas?

Thanks

Dave

 

[Lynn Trapp]

With a relational database, I'm afraid you will have to "pre-define" your
levels, although, it is possible to nest levels inside a table using a
modified "bill of materials" design. Basically, you would need a field in
the table that identified which other record the current record is related
to. It's not an easy thing to do and get right, but it can be done.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[mscertified]

You can define a single table to handle this
e.g.
Key
<data columns>
ParentKey (points to Key)

-Dorian

 

[mnature]

I would like to mimic this behaviour in Access. I know it can be done

because MS Project can be saved as a database and opened with Access.

Even though you can save a nested MS Project as a database, that database is
not dynamic in the same way that MS Project was. If it was dynamic, then you
could just develop what you need in MS Project and make that into a database.
The suggestion by mscertified will be the closest you can get to what you
want.

 

[David M C]

The individual tasks in Project can be nested to any (or at least a very
deep) level. Having looked at the table design in the Project Database I
think they do it in the way suggested by the posters above. Obviously, it
takes quite a lot of coding to manage this structure and display it correctly.

 

[Lynn Trapp]

Yes, it takes a lot to manage and display it. That would be my biggest
concern for what you want to do -- how to create a form that will allow for
data entry and display. Unless you are dealing with a very large database, I
would tend to keep the thing in Project.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

 

[mscertified]

Its actually not that difficult if you are willing to accept some limitations
in how it is displayed and manipulated. I have an application that does it.
It displays the top level keys with plus signs if there are lower levels,
when you click on these it goes down a level. There are command buttons go up
and down levels and to add or delete nodes. It took me a couple of weeks to
write.

-Dorian