Using Access to Plan Project Activities 2

[Troyston]

Hi,

I'm trying to use access to plan project activities using the theory of predecessors and successors I have the following tables:

Milestones
Predecessors
Successors
SuccessorSteps

SuccessorSteps provides a mechanism to store the criteria used to step from a milestone to it's successor and the duration of the step.

I'm able to isolate the first milestone and iterate through the steps table building up all of the relevant criteria to make the step then (on teh fly) make a query to update the duration of teh successor activity.

Problem is, what to do now, I can't figure out the recursive logic required to allocate durations to all successive activities. I realise that I need to make teh successor the milestone then get the successors based on that value but my brain ca't get around the recursion.

The plans to be automated are short but there are 2000 of them at a time and as the progress data comes from another system it's not feasible to egt people to do it manually.

Anyone have a sample of something simialr that they've done before that I can borrow to help solve this? Or perhpaps some pointers or suggestions on where to look.

I may have been looking at this too long for it to make sense and I've no doubt that divine intervention will solve all of my problems in a split second.

Thanks guys/gals.

Troyston

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[MajP]

I have done a lot of this. I have built front ends for MS Project using both Access and Outlook to produce Gant and Pert charts. I have also done similar things using Access to produce Gant/Pert/ schedules in either Treeviews or Excel.

My table structure is almost always the same. I have one table because an Event is an Event.

tblEvents
autoEventID
dtmEventStart
dtmEventEnd
intDuration
lngPredecessorID *Self referencing key
other descriptive event fields

With just this structure I know everything I need. I can do self referencing joins (between autoEventID and lngPredecessorID) to figure out time between events, predecessor/sucessor relationships etc.

Can you explain your key fields in the tables and how they link?

 

[ddiamond]

I'm able to isolate the first milestone and iterate through the steps table building up all of the relevant criteria to make the step then (on the fly) make a query to update the duration of the successor activity.

I take it this is what you are trying to automate for all milestones. Can you explain this process in a little more detail?

 

[Troyston]

OK, I get the first milesotne MS1 (no predecessors) then I can get the successors MS1.1...MS1.n

When I know the successosr I join the IDs to locate valid steps between the milestones:

ID Criteria Step
001-002 Category = GF 10 days
001-002 Category = RT 20 days
001-003 Category = EM 15 days

Then I update the schedule using, the milestone ID, criteria and step value, I do this by iterating through the steps and building the SQL as I go:

Do While Not .eof
UPDATE Schedule SET Duration = " & intDur & " WHERE " & strCriteria & ";"
.movenext
Loop

This works, now I want to use 002 as a Milestone and isolate successors from the successor table then apply the appropriate SQL updates eg:

ID Criteria Step
002-004 Category = GF 50 days
then
003-005 Category = RT 25 days

When I do this recursive traversal I want to do it breadth first rather than depth first.

CODE AS FOLLOWS:

Warning that there are additional factors such as programme and milestone template which are included in outer iterations.

Sub ModelAvailableSchedule()
On Error GoTo ErrorHandler
'---Recordset for acquisition of milestone labels, from progs-templates-milestones
Dim rst1 As Recordset
Dim rst2 As Recordset
Dim rst3 As Recordset
Dim rst4 As Recordset
'---Key values used in insert statements to relate transposed records to appropriate milestones etc
Dim intMSTmpltID As Long
Dim intMSID As Long
Dim intFirstMS_ID As Long
Dim intSecondMS_ID As Long
'---Counter
Dim intCnt As Integer
'---Step values
Dim intStepVal As Integer
Dim intStepValSlipped As Integer
'---Field labels, names etc
Dim strProgLbl As String
Dim strMS_ModelField As String
Dim strMS_FC_ModelField As String
Dim strMS_Act_ModelField As String
Dim strPotKey As String
Dim strMSStepID As String
Dim strGrouper As String
'-----------------------------------------------------------------------------------------------------------
'---
'---Model the available schedule
'---
'---In order to model the available schedule the dates for Phase Dates are ammended in ths current schedule.
'---This is done by updating the Phase dates for all schedule items where the various criteria specified in
'---the step form are met and where the milestone criteria are matched
'-----------------------------------------------------------------------------------------------------------
'---
'---Logic outlined as follows:
'--- 1. Get the list of templates and iterate logic across each template
'--- 2. Get the first milestone in each template, ie that with no predecessors
'---
'---Switch off echo and on hourglass
'---DoCmd.Hourglass True
'---Application.Echo False
Set dbs = DBEngine(0)(0)
'---Get a list of templates that exist in this schedule, this avoids iterations across templates for which
'---there are no records
strSQL = "SELECT DISTINCT Template " & _
"FROM TMP_ArtSch_Transposed " & _
"INNER JOIN SCH_Milestone ON TMP_ArtSch_Transposed.Template = SCH_Milestone.MS_Tmplt_ID;"
Set rst1 = dbs.OpenRecordset(strSQL)
With rst1
.MoveLast
.MoveFirst
Do While Not .EOF
'---Reference for tempalte
intMSTmpltID = !Template
'---Set up SQL for selection of the first milestone ie that with no predecessors, generally this is
'---Issued and the step will be to acquired, there are special condition for this step as not all
'--activities have been built into the modelling process and data structure
strSQL = "SELECT SCH_Milestone.MS_ID, MS_Tmplt_ID, MS_Name, MS_Pre_ID, MS_FC_ModelField, MS_Act_ModelField " & _
"FROM SCH_Milestone LEFT JOIN SCH_MS_Pre ON SCH_Milestone.MS_ID = SCH_MS_Pre.MS_ID " & _
"WHERE ((MS_Tmplt_ID = " & intMSTmpltID & ") AND (MS_Pre_ID Is Null) AND " & _
"(MS_FC_ModelField <> 'NA') AND (MS_Act_ModelField <> 'NA'));"
Set rst2 = dbs.OpenRecordset(strSQL)
With rst2
.MoveLast
.MoveFirst
intFirstMS_ID = !MS_ID
End With
'---Now get all consequesnt milestones, within this operation the update of the schedule must take place
'---Where there are multiple successors to a milestone only push the milestone forwards where the push
'---leaves the milestone in the future and does not pull it back, this would have the effect of making
'---another activitie's duration shorter
'---Switch off echo and on hourglass
DoCmd.Hourglass (False)
Application.Echo (True)
strSQL = "SELECT MSTmplt_ID, MS_ID, MS_Suc_ID " & _
"FROM SCH_MS_Suc " & _
"WHERE ((MSTmplt_ID = " & intMSTmpltID & ") AND (MS_ID = " & intFirstMS_ID & "));"
Set rst2 = dbs.OpenRecordset(strSQL)
With rst2
.MoveLast
.MoveFirst
Do While Not .EOF
intSecondMS_ID = !MS_Suc_ID
'---Concatenate first milestone ID with second milestone ID and use this string to identify all
'---successor step sets defining the transition between the milestones, these can be selected by
'---order of step set precedence, this means that the steps are applied in order of priority and
'---thus any sets that are not exclusive can be applied ahead of exclusive sets
strMSStepID = Format(intFirstMS_ID, "000") & "-" & Format(intSecondMS_ID, "000")
strSQL = "SELECT DISTINCT Left([MS_SucStep_ID],7) AS MS_Step_ID, StepGrouper, StepGrouperPrecedence " & _
"FROM SCH_MS_SucStep " & _
"WHERE (Left([MS_SucStep_ID], 7) = '" & strMSStepID & "') " & _
"ORDER BY StepGrouperPrecedence;"
Set rst3 = dbs.OpenRecordset(strSQL)
With rst3
.MoveLast
.MoveFirst
Do While Not .EOF
'---Reference to:
'--- 1. Milestone from - to
'--- 2. Step grouper
'---These represent a pseudo foreign key for grouping milestone steps
'---Having this it is possible to now build up the SQL: string for each step in the
'---correct order using the SQL parts in the cardinal order and get the step in days
'---Then an update statement to move the TO milestone into the future can be obtained
strGrouper = !StepGrouper
strSQL = "SELECT DISTINCT Left([MS_SucStep_ID],7) AS MS_Step_ID, StepGrouper, CardinalPos, SQLPart, StepVal, StepValueSlipped " & _
"FROM SCH_MS_SucStep " & _
"WHERE ((Left([MS_SucStep_ID],7) = '" & strMSStepID & "') AND " & _
"(StepGrouper ='" & strGrouper & "')) " & _
"ORDER BY CardinalPos;"
Set rst4 = dbs.OpenRecordset(strSQL)
With rst4
.MoveLast
.MoveFirst
strSQL = ""
intStepVal = !StepVal
intStepValSlipped = !StepValueSlipped
strGrouper = !StepGrouper
Do While Not .EOF
strSQL = strSQL & !SQLPart
.MoveNext
Loop
strSQL = "UPDATE TMP_ArtSch_Transposed " & _
"SET " & _
"ModelMS_Mdl_Dur = " & intStepVal & ", " & _
"ModelMS_Mdl_DurSlip = " & intStepValSlipped & " " & _
"WHERE " & strSQL & " AND ((ModelMS_ID = " & intSecondMS_ID & ") AND (ModelThis = TRUE));"
dbs.Execute strSQL
Debug.Print strSQL
End With
.MoveNext
Loop
End With
.MoveNext
Loop
End With
'---Move to next template in list of valid templates
.MoveNext
'---Loop to next template in valid templates recordset
Loop
'---End with for templates recordset
End With
DoCmd.Hourglass False
Application.Echo True
'---Get out here
ExitPoint:
Set rst = Nothing
Set dbs = DBEngine(0)(0)
For Each r In dbs.Recordsets
r.Close
Next r
Set dbs = Nothing
DoCmd.Hourglass False
Application.Echo True
SysMsg = SysCmd(acSysCmdClearStatus)
Exit Sub
'---Handle errors here
ErrorHandler:
intErr = Err.Number
Select Case intErr
Case Is > 0
strRtnName = "ModelAvailableSchedule"
Call HandleAllErrorrs(intErr, strRtnName)
Resume ExitPoint
End Select
End Sub




Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[MichaelRed]

Maj " ... built front ends for MS Project using both Access and Outlook to produce Gant and Pert charts ... "

Any chance you would be willing to share a sample?



MichaelRed

 

[MajP]

Michael,
The stuff I got may not be too earth shaking for what you are looking for. Most of the stuff I was doing was not to build detail charts for one project, but show a macro view of multiple projects. The Gantts are very simple with events that have start and end times. There is no accounting for dependencies such as "no earlier", "no later", "start before", etc. The way the Outlook thing worked was that we were using a Public calendar with custom forms. So every appointment had a few custom fields such as "Program Name", "Event Type" that had to be filled in. Then I read through each appointment item sorted by Program Name and then by Start Date. Each Program became a node in Project and each Appointment Item became a sub node with a Start Date and End Date. It gave a simple view of multiple Programs with multiple events. People liked it because for most people Project has a lot of overhead that they do not use, but most people are comfortable with Outlook. A lot of people used the interface to layout the skeleton for individual projects. The Access stuff is similar. I will see what I can find if this is still of interest.
Of more interest is the data structure I proposed in my post. I have a table with 10,000 military organizations. Each organization has a self referencing "PredecessorID" foriegn key that points to the tables primary key of a units "higher" unit. With just this structure I can build a treeview organizational diagram with multiple level of branches using recursion.

 

[Troyston]

MajP,

I've got the predecessor/successor thing sorted and I additionaly use milestone tempaltes since there are around 2000 plans fitting into a discrete number of summary templates.

Historically the method was iterative hard coded:

Do MS1
events, criteria, durations
Do MS2
events, criteria, durations
Do MS3
events, criteria, durations
Do MS4
events, criteria, durations
loop
loop
loop
loop

However, this is very cumbersome and doesn't allow the organisation to change the various criteria and step durations between milestones, add more milestones, take milestones away or change dependencies. Hence I've got all of the pre work done, tables, user interface etc for building programmes, milestone templates, milestones, criteria, durations etc and I can get the the first milesotne in the first tempalte in teh first programme and add teh appropriate duration. Then I can do teh same for he first milestone for al other tempaltes in all other programmes. However, I'm stuck on the bit where I need to get from MS1 to MS1.1 and MS1.2 then MS1.2 to MS1.2.1 and so on.

My alternative is to write the information to MS Proj but even then I need to write out the milesotnes (which is simple) and assess the duration of th esteps based on teh relevant criteria (which is difficult - for me).

I'm sure that there is an algorithmic solution to this that someone figured out a long time ago, I just need to figure that then how to amend it or fit it into my schema.

I'm feeling as if I'm on the cusp of rediscovering the wheel.

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk

 

[MajP]

This example of a recursive call may help. I used this to build a treeview where there is an unknown level of sub branches for every branch. The key here is passing the recordset back to the sub routine. I think this logic is similar to what you want.

Private Sub subAddBranch([b]myRS As Recordset, theCurrentID As String[/b])
  Dim strCriteria As String
  Dim bk As String
  Dim currentTaskNumber As String
  Dim currentHigherTask As String
  Dim currentTaskDescription As String
  
   currentTaskNumber = theCurrentID
   strCriteria = "txtParentUIC = '" & currentTaskNumber & "'"
  ‘Return all of the children to the current node through recursion
  myRS.FindFirst (strCriteria)
   [b] Do Until myRS.NoMatch [/b]
      currentTaskNumber = myRS![txtUICcode]
       currentHigherTask = myRS![txtParentUIC]
    If currentHigherTask = "W" Then
      ‘Code to add Root level nodes 
   Else
     ‘Code to add subordinate (non-root level) nodes
End If
  ‘Mark where your at in the recordset
bk = myRS.Bookmark
‘recursive call passing the recordset
   [b]Call subAddBranch(myRS, currentTaskNumber)
    ‘as you fall out of the recursion return to where you where in the recordset
    myRS.Bookmark = bk
    myRS.FindNext (strCriteria) [/b]
  Loop
End Sub

Here is how this would build

Node1
-------------------------------
Node1
Node1.1
-------------------------------
Node1
Node1.1
Node 1.1.1
-------------------------------
Node1
Node1.1
Node 1.1.1
Node 1.1.2
-------------------------------
Node1
Node1.1
Node 1.1.1
Node 1.1.2
Node 1.2
-------------------------------
Node1
Node1.1
Node 1.1.1
Node 1.1.2
Node 1.2
Node 1.3
-------------------------------
Node1
Node1.1
Node 1.1.1
Node 1.1.2
Node 1.2
Node 1.3
Node2
-------------------------------

 

[ddiamond]

Troy,

You mentioned that you need a breadth first traversal. I believe MajP's example is depth first (MajP, please correct me if I'm wrong). Will depth first work for you?

- Dan

 

[MichaelRed]

MajP

Actually, what you describe is QUITE close to the process I am contemplating for a small company. They, as you say, are comfortable with using Outlook - but their capability is at the scratch the surface of the out-of-the-box user interface. I have done some prelininary work and m able to get the info they enter into outlook (appointments calendar) into Ms. Access. The actual tiem / labor is recorded in an Ms. Access db already and was at the point of starting to layout the charts to show the historical part from the gathered labor data and the forsscast part from the OUtlook Appointment(s) for the multiple jobs tracking. What you describe seems like a close fit (including their reluctance to use the industrial strength Project Management Tool(s).

I think from what you have said and my experience with a number of smaller organizations in this realm that you could provide a really valuable tool set (perhaps via a faq?). I'm sure that I would be interested and provide some feedback or other assistance in development is that would encourage you.



MichaelRed

 

[MajP]

This may be closer.

TaskID intDuration PredecessorID

TaskRed 20 None
TaskWhite 10 TaskRed
TaskBlue 30 TaskWhite
TaskYellow 10 TaskBlue
TaskGreen 40 TaskYellow

The time from the first task "Red" to the end is 20+10+30+10 + 40 = 110
The time from White to the end is 90
etc.

Public Sub TimesToCompletion()
  Dim rs As DAO.Recordset
  Dim bk As String
  Set rs = CurrentDb.OpenRecordset("tblTasks", dbOpenDynaset)
  Do While Not rs.EOF
    Debug.Print rs.Fields("TaskID")
    bk = rs.Bookmark
    Debug.Print getTimeToComplete(rs, rs.Fields("PredecessorID"))
    rs.Bookmark = bk
    rs.MoveNext
  Loop
End Sub

Public Function getTimeToComplete(ByRef rs As DAO.Recordset, strCurrentTask As String) As Long
  Dim strCriteria As String
  Dim strPredecessorTask
  strCriteria = "PredecessorID = '" & strCurrentTask & "'"
  rs.FindFirst (strCriteria)
  Do Until rs.NoMatch
    strCurrentTask = rs.Fields("TaskID")
    getTimeToComplete = rs.Fields("intDuration") + getTimeToComplete(rs, strCurrentTask)
  Loop
End Function

Output from above which I would stick into a field for each record.

TaskRed
110
TaskWhite
90
TaskBlue
80
TaskYellow
50
TaskGreen
40

Also you get the correct answers regardless of the order of the recordset. Not the most efficient algorithm, but it spans the depth for each node.

 

[Troyston]

Maj Michael Dan,

Between the posts I think now that the depth first recursive approach will enable me to step from milesotne to milestone and determine the duration of the step based on the task and peripheral definition criteria. Although I'm not sure how I will do this at the moment.

I'll take a look at the problem again next week when I get some time again. For the meantime I have hand coded all of the steps because I didn't have time to do it properly. You're right, the company doesn't have the foresight to invest in the right tools and this kind of intelligence will offer them real ground breaking mass automatic planning capabilities.

It's a bit untidy at the moment perhaps I could let you know how I got on with. I'd love to have the opportunity for a face to face with you guys and get some ideas.

Troy Vinson
Trading as IT Supportman

http://www.itsupportman.co.uk