Developing planning database

[meghanwh]

I'm working to develop a planning database for a client, and
essentially the client has an outline with different tasks and
subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
basically at a loss as to how to get these all designed in access, it
seems like the task number could be the primary key, but how do I set
it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
greatly appreciated. Thanks.

 

[meghanwh]

I'm working to develop a planning database for a client, and
essentially the client has an outline with different tasks and
subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
basically at a loss as to how to get these all designed in access, it
seems like the task number could be the primary key, but how do I set
it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
greatly appreciated. Thanks.

OK, I think if I break it down into SubTasks, so I have Task 1, then
SubTask 2 and SubTask3, which would combine to task 1.2.3. I've
assigned each subtask its own ID number, automatically assigned (an
arbitrary number). The question I have now is that there are up to 6
degrees of tasks, and what's the best way to get it so that if the
fields of Task, SubTask1, Subtask2, etc. are all filled out there's a
text box on a form that calculates it to actually read "1.2.3.4.5.6"?
I tried to just use the regular string with a whole slew of
"IIF(IsNull[SubTask2])..." but it got too complicated with up to 6
subtasks. Thanks.

 

[JK]

I would create a table for each level i.e.:

tblMainTasks
Task_ID (Key)
Task Number (Integer - your 1,2,3,4 etc)
TaskName
etc.

TblLvl2Tasks
L2_ID (key)
Task_ID (forgein Key - linked to tblMainTasks)
L3vel2 Number 1.1, 1.2, 2.1, 3.2 etc
L2Name
ect

tblLvl3Tasks
L3_ID (key)
L2_ID (FK linked to tbllvl2Tasks)
Level3 Number (1.1.1, 1.1.2 etc

etc.
etc.
etc.

Each level is has a One-To-Many relation (downward) to its "child" level.

Comments:
You can nest all the levels in sub form (max 7 deep) but it is not
necessary, and will be quite cumbersome one you go past 3 levels down.

In order to establish whether a (main) task has been completed you nest
searches through its sub-tasks and respectively through each of their
"children", grand children grand grand children and so forth.

The numbering (1.1.2) is redundant as you can search sub-tasks without it
but you may wish to keep the numbering or other reasons.

Regards/JK









| I'm working to develop a planning database for a client, and
| essentially the client has an outline with different tasks and
| subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
| etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
| order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
| basically at a loss as to how to get these all designed in access, it
| seems like the task number could be the primary key, but how do I set
| it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
| greatly appreciated. Thanks.
|

 

[JK]

Indeed so, going down *up to* 6 levels is complicated. In principle, you
eliminate the lower levels upwards, by filtering out completed sub tasks or
by other means, depending how you are flagging completed subtasks.

Regards/JK

| On Sep 17, 9:22 pm, (e-mail address removed) wrote:
| > I'm working to develop a planning database for a client, and
| > essentially the client has an outline with different tasks and
| > subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
| > etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
| > order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
| > basically at a loss as to how to get these all designed in access, it
| > seems like the task number could be the primary key, but how do I set
| > it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
| > greatly appreciated. Thanks.
|
| OK, I think if I break it down into SubTasks, so I have Task 1, then
| SubTask 2 and SubTask3, which would combine to task 1.2.3. I've
| assigned each subtask its own ID number, automatically assigned (an
| arbitrary number). The question I have now is that there are up to 6
| degrees of tasks, and what's the best way to get it so that if the
| fields of Task, SubTask1, Subtask2, etc. are all filled out there's a
| text box on a form that calculates it to actually read "1.2.3.4.5.6"?
| I tried to just use the regular string with a whole slew of
| "IIF(IsNull[SubTask2])..." but it got too complicated with up to 6
| subtasks. Thanks.
|

 

[Paul Shapiro]

Would a project management package, like Microsoft Project, be a better
solution than a custom database? If you need to add more automation and/or
features, I think you can write code in Project too, or else interface to
the Project data from MS Access. I exported Access data to Project a number
of years ago just for displaying and printing schedules, because it would
have taken months to build a comparable user interface in Access. I know
Project supports this kind of task structure, although I don't know if they
limit the number of levels.

 

[Pieter Wijnen]

One Table Approach (Listing only Significant Fields)

Task
TaskID (pk)
ParentTask (fk)

Usage (in Query - for treeview)

SELECT TaskID, ParentTask, 1 As Lvl FROM Task T1
WHERE ParentTask Is Null
UNION ALL
SELECT TaskID, ParentTask, 2 As Lvl FROM Task T2
WHERE EXISTS (SELECT 'X' FROM Task T1
WHERE T1.TaskID=T2.ParentTask
AND T1.ParentTask Is Null)
UNION ALL
SELECT TaskID, ParentTask, 3 As Lvl FROM Task T3
WHERE EXISTS (SELECT 'X' FROM Task T1 Inner Join Task T2
ON T1.TaskID=T2.ParentTask
WHERE T2.TaskID=T3.ParentTask
AND T1.ParentTask Is Null)
UNION ALL
SELECT TaskID, ParentTask, 4 As Lvl FROM Task T4
WHERE EXISTS (SELECT 'X' FROM Task T1 Inner Join (Task T2 Inner Join Task T3
ON T2.TaskID=T3.ParentTask)
ON T1.TaskID=T2.ParentTask
WHERE T3.TaskID=T4.ParentTask
AND T1.ParentTask Is Null)
....
ORDER By Lvl, TaskID

You may have to have a field for the articles (redundant) to build up the
string 1.2.1.4
To ensure uniqueness you'd then have a unique index on
TaskID, Article - where Article is a numeric, this you can build by moving
(part of) the Exists sub-selects into the Main Select at each level
And if you were using Oracle it would be childs play using the 'connect by
prior' construct & Level reserved 'label' <g>

HTH

Pieter

I'm working to develop a planning database for a client, and
essentially the client has an outline with different tasks and
subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
basically at a loss as to how to get these all designed in access, it
seems like the task number could be the primary key, but how do I set
it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
greatly appreciated. Thanks.

OK, I think if I break it down into SubTasks, so I have Task 1, then
SubTask 2 and SubTask3, which would combine to task 1.2.3. I've
assigned each subtask its own ID number, automatically assigned (an
arbitrary number). The question I have now is that there are up to 6
degrees of tasks, and what's the best way to get it so that if the
fields of Task, SubTask1, Subtask2, etc. are all filled out there's a
text box on a form that calculates it to actually read "1.2.3.4.5.6"?
I tried to just use the regular string with a whole slew of
"IIF(IsNull[SubTask2])..." but it got too complicated with up to 6
subtasks. Thanks.

 

[meghanwh]

Indeed so, going down *up to* 6 levels is complicated. In principle, you
eliminate the lower levels upwards, by filtering out completed sub tasks or
by other means, depending how you are flagging completed subtasks.

Regards/JK


| On Sep 17, 9:22 pm, (e-mail address removed) wrote:
| > I'm working to develop a planning database for a client, and
| > essentially the client has an outline with different tasks and
| > subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
| > etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
| > order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
| > basically at a loss as to how to get these all designed in access, it
| > seems like the task number could be the primary key, but how do I set
| > it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
| > greatly appreciated. Thanks.
|
| OK, I think if I break it down into SubTasks, so I have Task 1, then
| SubTask 2 and SubTask3, which would combine to task 1.2.3. I've
| assigned each subtask its own ID number, automatically assigned (an
| arbitrary number). The question I have now is that there are up to 6
| degrees of tasks, and what's the best way to get it so that if the
| fields of Task, SubTask1, Subtask2, etc. are all filled out there's a
| text box on a form that calculates it to actually read "1.2.3.4.5.6"?
| I tried to just use the regular string with a whole slew of
| "IIF(IsNull[SubTask2])..." but it got too complicated with up to 6
| subtasks. Thanks.
|

Thanks. I now have them all on separate tables, and have created a
form (frmMainGoals) with the top level task based on the tblMainGoals,
and then I tried to put a button on the form that would lead me to
that main task's subtasks, so I created another form based on
tblLevel2Goals and a button on frmMainGoals. The code for the button
is:
Private Sub Level2Form_Click()
On Error GoTo Err_Level2Form_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmLevel2Goals"

stLinkCriteria = "[MainGoalID]=" & Me![GoalID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Level2Form_Click:
Exit Sub

Err_Level2Form_Click:
MsgBox Err.Description
Resume Exit_Level2Form_Click

End Sub

When I click the button, however, it comes up with an error message
that the action can't be performed. I tried this with a query instead
of the table as the source for the frmLevel2Goals, but that came up
with the same message. Thoughts?

 

[JK]

The only action you have is opening the form. There is nothing wrong with
the syntax. Check spellings of field names and whether there is something
which prevents the form from opening.

The problem may well be in frmLevev2Goal. Does it open by itself manually?

Apology for the late reply.

Regards/Jacob



| > Indeed so, going down *up to* 6 levels is complicated. In principle,
you
| > eliminate the lower levels upwards, by filtering out completed sub tasks
or
| > by other means, depending how you are flagging completed subtasks.
| >
| > Regards/JK
| >
| >
| > | > | On Sep 17, 9:22 pm, (e-mail address removed) wrote:
| > | > I'm working to develop a planning database for a client, and
| > | > essentially the client has an outline with different tasks and
| > | > subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
| > | > etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
| > | > order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
| > | > basically at a loss as to how to get these all designed in access,
it
| > | > seems like the task number could be the primary key, but how do I
set
| > | > it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
| > | > greatly appreciated. Thanks.
| > |
| > | OK, I think if I break it down into SubTasks, so I have Task 1, then
| > | SubTask 2 and SubTask3, which would combine to task 1.2.3. I've
| > | assigned each subtask its own ID number, automatically assigned (an
| > | arbitrary number). The question I have now is that there are up to 6
| > | degrees of tasks, and what's the best way to get it so that if the
| > | fields of Task, SubTask1, Subtask2, etc. are all filled out there's a
| > | text box on a form that calculates it to actually read "1.2.3.4.5.6"?
| > | I tried to just use the regular string with a whole slew of
| > | "IIF(IsNull[SubTask2])..." but it got too complicated with up to 6
| > | subtasks. Thanks.
| > |
|
| Thanks. I now have them all on separate tables, and have created a
| form (frmMainGoals) with the top level task based on the tblMainGoals,
| and then I tried to put a button on the form that would lead me to
| that main task's subtasks, so I created another form based on
| tblLevel2Goals and a button on frmMainGoals. The code for the button
| is:
| Private Sub Level2Form_Click()
| On Error GoTo Err_Level2Form_Click
|
| Dim stDocName As String
| Dim stLinkCriteria As String
|
| stDocName = "frmLevel2Goals"
|
| stLinkCriteria = "[MainGoalID]=" & Me![GoalID]
| DoCmd.OpenForm stDocName, , , stLinkCriteria
|
| Exit_Level2Form_Click:
| Exit Sub
|
| Err_Level2Form_Click:
| MsgBox Err.Description
| Resume Exit_Level2Form_Click
|
| End Sub
|
| When I click the button, however, it comes up with an error message
| that the action can't be performed. I tried this with a query instead
| of the table as the source for the frmLevel2Goals, but that came up
| with the same message. Thoughts?
|

 

[meghanwh]

The only action you have is opening the form. There is nothing wrong with
the syntax. Check spellings of field names and whether there is something
which prevents the form from opening.

The problem may well be in frmLevev2Goal. Does it open by itself manually?

Apology for the late reply.

Regards/Jacob


| > Indeed so, going down *up to* 6 levels is complicated. In principle,
you
| > eliminate the lower levels upwards, by filtering out completed sub tasks
or
| > by other means, depending how you are flagging completed subtasks.
| >
| > Regards/JK

| >
| >| > | On Sep 17, 9:22 pm, (e-mail address removed) wrote:
| > | > I'm working to develop a planning database for a client, and
| > | > essentially the client has an outline with different tasks and
| > | > subtasks. For instance, you have task 1 and then sub-tasks 1.1, 1.2,
| > | > etc., and to even further degrees, all the way to 1.2.3.4.5.6. In
| > | > order to achieve task 1, you must complete 1.1, 1.2, etc. I'm
| > | > basically at a loss as to how to get these all designed in access,
it
| > | > seems like the task number could be the primary key, but how do I
set
| > | > it up so access knows that 1.2.1 is a subtask of 1.2? Any help is
| > | > greatly appreciated. Thanks.
| > |
| > | OK, I think if I break it down into SubTasks, so I have Task 1, then
| > | SubTask 2 and SubTask3, which would combine to task 1.2.3. I've
| > | assigned each subtask its own ID number, automatically assigned (an
| > | arbitrary number). The question I have now is that there are up to 6
| > | degrees of tasks, and what's the best way to get it so that if the
| > | fields of Task, SubTask1, Subtask2, etc. are all filled out there's a
| > | text box on a form that calculates it to actually read "1.2.3.4.5.6"?
| > | I tried to just use the regular string with a whole slew of
| > | "IIF(IsNull[SubTask2])..." but it got too complicated with up to 6
| > | subtasks. Thanks.
| > |
|
| Thanks. I now have them all on separate tables, and have created a
| form (frmMainGoals) with the top level task based on the tblMainGoals,
| and then I tried to put a button on the form that would lead me to
| that main task's subtasks, so I created another form based on
| tblLevel2Goals and a button on frmMainGoals. The code for the button
| is:
| Private Sub Level2Form_Click()
| On Error GoTo Err_Level2Form_Click
|
| Dim stDocName As String
| Dim stLinkCriteria As String
|
| stDocName = "frmLevel2Goals"
|
| stLinkCriteria = "[MainGoalID]=" & Me![GoalID]
| DoCmd.OpenForm stDocName, , , stLinkCriteria
|
| Exit_Level2Form_Click:
| Exit Sub
|
| Err_Level2Form_Click:
| MsgBox Err.Description
| Resume Exit_Level2Form_Click
|
| End Sub
|
| When I click the button, however, it comes up with an error message
| that the action can't be performed. I tried this with a query instead
| of the table as the source for the frmLevel2Goals, but that came up
| with the same message. Thoughts?
|

Thanks so much Jacob. I played around with it and found that if I
based the form on a query instead of a table then i was able to get
the results I was looking for. Thanks very much for all your help.
meghan.