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)

[CODE=vb]

Private Sub btnMSProject_Cl ick()

Dim prjProject As MSProject.Proje ct
Dim appMSProject As MSProject.Appli cation
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("M SProject.Applic ation")
Set prjProject = appMSProject.Pr ojects.Add

strSQL = "SELECT * FROM tblUsers;"
rsUsers.Open strSQL, CurrentProject. Connection, _
adOpenForwardOn ly, adLockReadOnly

While Not rsUsers.EOF

Set tskTask = prjProject.Task s.Add
tskTask.Name = rsUsers![txtName]

strSQL = "SELECT * FROM tblBookings WHERE keyUserID=" & rsUsers![keyUserID] & _
" ORDER BY dteBookedOn ASC;"
rsBookings.Open strSQL, CurrentProject. Connection, _
adOpenDynamic, adLockOptimisti c

With tskTask
rsBookings.Move First
appMSProject.Pr ojectSummaryInf o _
Start:=DateAdd( "d", -1, rsBookings![dteBookedOn])
dteDate = rsBookings![dteBookedOn]
.Start = dteDate
rsBookings.Move Last
dteDate = DateAdd("d", Nz(rsBookings![intDays], 1) - 1, _
rsBookings![dteBookedOn])
.Finish = dteDate
rsBookings.Move First
End With

Do
dteStart = DateAdd("d", Nz(rsBookings![intDays], 1), _
rsBookings![dteBookedOn])
rsBookings.Move Next
If Not rsBookings.EOF Then
dteEnd = rsBookings![dteBookedOn]
tskTask.Split dteStart, dteEnd
Else
tskTask.Finish = dteStart
Exit Do
End If
Loop

rsBookings.Clos e
rsUsers.MoveNex t

Wend

rsUsers.Close
appMSProject.Vi sible = True

Set tskTask = Nothing
Set rsBookings = Nothing
Set rsUsers = Nothing
Set prjProject = Nothing
Set appMSProject = Nothing

End Sub

[/CODE]

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.

[CODE=vb]

Private Sub btnMSProject_Cl ick()

Dim prjProject As MSProject.Proje ct
Dim appMSProject As MSProject.Appli cation
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("M SProject.Applic ation")
Set prjProject = appMSProject.Pr ojects.Add

'---- Set all week days as working because Task.Split method is too clever
For Each wd In prjProject.Cale ndar.WeekDays
wd.Working = True
Next

strSQL = "SELECT * FROM tblUsers;"
rsUsers.Open strSQL, CurrentProject. Connection, _
adOpenForwardOn ly, adLockReadOnly

While Not rsUsers.EOF

Set tskTask = prjProject.Task s.Add
tskTask.Name = rsUsers![txtName]

strSQL = "SELECT * FROM tblBookings WHERE keyUserID=" & rsUsers![keyUserID] & _
" ORDER BY dteBookedOn ASC;"
rsBookings.Open strSQL, CurrentProject. Connection, _
adOpenDynamic, adLockOptimisti c

With tskTask
rsBookings.Move First
appMSProject.Pr ojectSummaryInf o _
Start:=DateAdd( "d", -1, rsBookings![dteBookedOn])
dteDate = rsBookings![dteBookedOn]
.Start = dteDate
rsBookings.Move Last
dteDate = DateAdd("d", Nz(rsBookings![intDays], 1) - 1, _
rsBookings![dteBookedOn])
.Finish = dteDate
rsBookings.Move First
End With

Do
dteStart = DateAdd("d", Nz(rsBookings![intDays], 1), _
rsBookings![dteBookedOn])
rsBookings.Move Next
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.Clos e
rsUsers.MoveNex t

Wend

rsUsers.Close
appMSProject.Vi sible = True

Set tskTask = Nothing
Set rsBookings = Nothing
Set rsUsers = Nothing
Set prjProject = Nothing
Set appMSProject = Nothing

End Sub

[/CODE]

[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.