Timeline display

[berndh]

Hi all,

I'm working on a litle project and have run into a snag.

I would like to read the starting/ending dates of specific users and display then in a timeline.

This is just to show when a user is booked and for which days ie

Can anybody assist in how to create something like this in Access?

Regards

[ADezii]

Hi all,

I'm working on a litle project and have run into a snag.

I would like to read the starting/ending dates of specific users and display then in a timeline.

This is just to show when a user is booked and for which days ie

Can anybody assist in how to create something like this in Access?

Regards

I do not know of any mechanism within Access by which this could easily be accomplished. Off the top of my head, the closest approximation would be a series of 7 Check Boxes (M thru S) for any given Date which could then be checked and give some visual indication of a Time Line but this seems a little far fetched (2,555 Check Boxes for a given Year per person). Give me a little time to think on it.

[FishVal]

Hi all,

I'm working on a litle project and have run into a snag.

I would like to read the starting/ending dates of specific users and display then in a timeline.

This is just to show when a user is booked and for which days ie

Can anybody assist in how to create something like this in Access?

Regards

The image you've provided resembles MSProject document. Have you think about exporting your data to MSProject document opened either as separate application or as Unbound object control on a form ?

[berndh]

It looks very similar to MS Project indeed, but was designed in another package using the web as front-end and SQL as a backend, I was just given a sample to see if it can be done in ACCESS to save on costs.

I was thinking along the lines of 365 boxes in a table, start gets an image and everyting between another, the ending then gets another image to make it look nice???

STUCK...

[FishVal]

It looks very similar to MS Project indeed, but was designed in another package using the web as front-end and SQL as a backend, I was just given a sample to see if it can be done in ACCESS to save on costs.

I was thinking along the lines of 365 boxes in a table, start gets an image and everyting between another, the ending then gets another image to make it look nice???

STUCK...

If I've understood what do you mean, you want to solve it via sql queries.
I'm not sure whether this solution will succeed.

I reccomend you the following:
1) you've said that this was previously implemented using some Activex control, have you thought about using this control in Access
2) you may obtain Activex control implementing Gantt's chart presentation
3) you may use Excel
4) and at last you may use MSProject -

hereby is my solution

tblUsers
keyUserID (PK, Long(Autonumber))
txtName (Text)

tblBookings
keyBookingID (PK, Long(Autonumber))
keyUserID (FK, Long(Autonumber))
dteBookedOn (Date)
intDays (Integer)

 
Private Sub btnMSProject_Click()
 
    Dim prjProject As MSProject.Project
    Dim appMSProject As MSProject.Application
    Dim tskTask As MSProject.Task
    Dim rsUsers As New ADODB.Recordset
    Dim rsBookings As New ADODB.Recordset
    Dim sqlQuery As New SQLSelect
    Dim strSQL As String
    Dim dteDate As Date, dteStart As Date, dteEnd As Date
 
    Set appMSProject = CreateObject("MSProject.Application")
    Set prjProject = appMSProject.Projects.Add
 
    strSQL = "SELECT * FROM tblUsers;"
    rsUsers.Open strSQL, CurrentProject.Connection, _
        adOpenForwardOnly, adLockReadOnly
 
    While Not rsUsers.EOF
 
        Set tskTask = prjProject.Tasks.Add
        tskTask.Name = rsUsers![txtName]
 
        strSQL = "SELECT * FROM tblBookings WHERE keyUserID=" & rsUsers![keyUserID] & _
            " ORDER BY dteBookedOn ASC;"
        rsBookings.Open strSQL, CurrentProject.Connection, _
            adOpenDynamic, adLockOptimistic
 
        With tskTask
            rsBookings.MoveFirst
            appMSProject.ProjectSummaryInfo _
                Start:=DateAdd("d", -1, rsBookings![dteBookedOn])
            dteDate = rsBookings![dteBookedOn]
            .Start = dteDate
            rsBookings.MoveLast
            dteDate = DateAdd("d", Nz(rsBookings![intDays], 1) - 1, _
                rsBookings![dteBookedOn])
            .Finish = dteDate
            rsBookings.MoveFirst
        End With
 
        Do
            dteStart = DateAdd("d", Nz(rsBookings![intDays], 1), _
                rsBookings![dteBookedOn])
            rsBookings.MoveNext
            If Not rsBookings.EOF Then
                dteEnd = rsBookings![dteBookedOn]
                tskTask.Split dteStart, dteEnd
            Else
                tskTask.Finish = dteStart
                Exit Do
            End If
        Loop
 
        rsBookings.Close
        rsUsers.MoveNext
 
    Wend
 
    rsUsers.Close
    appMSProject.Visible = True
 
    Set tskTask = Nothing
    Set rsBookings = Nothing
    Set rsUsers = Nothing
    Set prjProject = Nothing
    Set appMSProject = Nothing
 
End Sub
 
 

Good Luck

[FishVal]

Sorry, a bug was detected. Method Task.Split check whether it falls into nonworking days and do what it supposes to be right.
So I've added code which sets all week days to working.

 
Private Sub btnMSProject_Click()
 
    Dim prjProject As MSProject.Project
    Dim appMSProject As MSProject.Application
    Dim tskTask As MSProject.Task
    Dim rsUsers As New ADODB.Recordset
    Dim rsBookings As New ADODB.Recordset
    Dim sqlQuery As New SQLSelect
    Dim strSQL As String
    Dim dteDate As Date, dteStart As Date, dteEnd As Date
 
    Set appMSProject = CreateObject("MSProject.Application")
    Set prjProject = appMSProject.Projects.Add
 
    '---- Set all week days as working because Task.Split method is too clever
    For Each wd In prjProject.Calendar.WeekDays
        wd.Working = True
    Next
 
    strSQL = "SELECT * FROM tblUsers;"
    rsUsers.Open strSQL, CurrentProject.Connection, _
        adOpenForwardOnly, adLockReadOnly
 
    While Not rsUsers.EOF
 
        Set tskTask = prjProject.Tasks.Add
        tskTask.Name = rsUsers![txtName]
 
        strSQL = "SELECT * FROM tblBookings WHERE keyUserID=" & rsUsers![keyUserID] & _
            " ORDER BY dteBookedOn ASC;"
        rsBookings.Open strSQL, CurrentProject.Connection, _
            adOpenDynamic, adLockOptimistic
 
        With tskTask
            rsBookings.MoveFirst
            appMSProject.ProjectSummaryInfo _
                Start:=DateAdd("d", -1, rsBookings![dteBookedOn])
            dteDate = rsBookings![dteBookedOn]
            .Start = dteDate
            rsBookings.MoveLast
            dteDate = DateAdd("d", Nz(rsBookings![intDays], 1) - 1, _
                rsBookings![dteBookedOn])
            .Finish = dteDate
            rsBookings.MoveFirst
        End With
 
        Do
            dteStart = DateAdd("d", Nz(rsBookings![intDays], 1), _
                rsBookings![dteBookedOn])
            rsBookings.MoveNext
            If Not rsBookings.EOF Then
                dteEnd = rsBookings![dteBookedOn]
                If dteStart < dteEnd Then tskTask.Split dteStart, dteEnd
            Else
                tskTask.Finish = dteStart
                Exit Do
            End If
        Loop
 
        rsBookings.Close
        rsUsers.MoveNext
 
    Wend
 
    rsUsers.Close
    appMSProject.Visible = True
 
    Set tskTask = Nothing
    Set rsBookings = Nothing
    Set rsUsers = Nothing
    Set prjProject = Nothing
    Set appMSProject = Nothing
 
End Sub
 
 

[garethfx]

Have a look at this. It may give you some ideas without going to heavily in to coding



http://www.access-programmers.co.uk/forums/showthread.php?t=108369

[berndh]

Stunning, thanks for the input. Will fiddle.

[berndh]

Hi again,

Problem....

As far as I understand, Gantt charts do no allow for "multiple item" listings per line...

Ie, we have Paul and it shows him to have two appointments (or three or four etc), underneath him would be Mary, with a few appointments etc etc, now there needs to be one line per person / resource which shows they availebility. A gantt chart will add a new line for every new event..

There was a web page created by somebody else, I do not have the code or the sample as it was a proposal from their side, just WAY tooooo expensive, hence it has been deleted.

[FishVal]

Hi again,

Problem....

As far as I understand, Gantt charts do no allow for "multiple item" listings per line...

Ie, we have Paul and it shows him to have two appointments (or three or four etc), underneath him would be Mary, with a few appointments etc etc, now there needs to be one line per person / resource which shows they availebility. A gantt chart will add a new line for every new event..

There was a web page created by somebody else, I do not have the code or the sample as it was a proposal from their side, just WAY tooooo expensive, hence it has been deleted.

Hi!

The code in #6 displays exactly what you want. The only question is whether MSProject is too expensive for you or not.