By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,549 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Timeline display

P: 7
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
Jun 6 '07 #1
Share this Question
Share on Google+
9 Replies


ADezii
Expert 5K+
P: 8,599
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.
Jun 7 '07 #2

FishVal
Expert 2.5K+
P: 2,653
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 ?
Jun 8 '07 #3

P: 7
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...
Jun 8 '07 #4

FishVal
Expert 2.5K+
P: 2,653
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)

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnMSProject_Click()
  3.  
  4.     Dim prjProject As MSProject.Project
  5.     Dim appMSProject As MSProject.Application
  6.     Dim tskTask As MSProject.Task
  7.     Dim rsUsers As New ADODB.Recordset
  8.     Dim rsBookings As New ADODB.Recordset
  9.     Dim sqlQuery As New SQLSelect
  10.     Dim strSQL As String
  11.     Dim dteDate As Date, dteStart As Date, dteEnd As Date
  12.  
  13.     Set appMSProject = CreateObject("MSProject.Application")
  14.     Set prjProject = appMSProject.Projects.Add
  15.  
  16.     strSQL = "SELECT * FROM tblUsers;"
  17.     rsUsers.Open strSQL, CurrentProject.Connection, _
  18.         adOpenForwardOnly, adLockReadOnly
  19.  
  20.     While Not rsUsers.EOF
  21.  
  22.         Set tskTask = prjProject.Tasks.Add
  23.         tskTask.Name = rsUsers![txtName]
  24.  
  25.         strSQL = "SELECT * FROM tblBookings WHERE keyUserID=" & rsUsers![keyUserID] & _
  26.             " ORDER BY dteBookedOn ASC;"
  27.         rsBookings.Open strSQL, CurrentProject.Connection, _
  28.             adOpenDynamic, adLockOptimistic
  29.  
  30.         With tskTask
  31.             rsBookings.MoveFirst
  32.             appMSProject.ProjectSummaryInfo _
  33.                 Start:=DateAdd("d", -1, rsBookings![dteBookedOn])
  34.             dteDate = rsBookings![dteBookedOn]
  35.             .Start = dteDate
  36.             rsBookings.MoveLast
  37.             dteDate = DateAdd("d", Nz(rsBookings![intDays], 1) - 1, _
  38.                 rsBookings![dteBookedOn])
  39.             .Finish = dteDate
  40.             rsBookings.MoveFirst
  41.         End With
  42.  
  43.         Do
  44.             dteStart = DateAdd("d", Nz(rsBookings![intDays], 1), _
  45.                 rsBookings![dteBookedOn])
  46.             rsBookings.MoveNext
  47.             If Not rsBookings.EOF Then
  48.                 dteEnd = rsBookings![dteBookedOn]
  49.                 tskTask.Split dteStart, dteEnd
  50.             Else
  51.                 tskTask.Finish = dteStart
  52.                 Exit Do
  53.             End If
  54.         Loop
  55.  
  56.         rsBookings.Close
  57.         rsUsers.MoveNext
  58.  
  59.     Wend
  60.  
  61.     rsUsers.Close
  62.     appMSProject.Visible = True
  63.  
  64.     Set tskTask = Nothing
  65.     Set rsBookings = Nothing
  66.     Set rsUsers = Nothing
  67.     Set prjProject = Nothing
  68.     Set appMSProject = Nothing
  69.  
  70. End Sub
  71.  
  72.  
Good Luck
Jun 9 '07 #5

FishVal
Expert 2.5K+
P: 2,653
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.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub btnMSProject_Click()
  3.  
  4.     Dim prjProject As MSProject.Project
  5.     Dim appMSProject As MSProject.Application
  6.     Dim tskTask As MSProject.Task
  7.     Dim rsUsers As New ADODB.Recordset
  8.     Dim rsBookings As New ADODB.Recordset
  9.     Dim sqlQuery As New SQLSelect
  10.     Dim strSQL As String
  11.     Dim dteDate As Date, dteStart As Date, dteEnd As Date
  12.  
  13.     Set appMSProject = CreateObject("MSProject.Application")
  14.     Set prjProject = appMSProject.Projects.Add
  15.  
  16.     '---- Set all week days as working because Task.Split method is too clever
  17.     For Each wd In prjProject.Calendar.WeekDays
  18.         wd.Working = True
  19.     Next
  20.  
  21.     strSQL = "SELECT * FROM tblUsers;"
  22.     rsUsers.Open strSQL, CurrentProject.Connection, _
  23.         adOpenForwardOnly, adLockReadOnly
  24.  
  25.     While Not rsUsers.EOF
  26.  
  27.         Set tskTask = prjProject.Tasks.Add
  28.         tskTask.Name = rsUsers![txtName]
  29.  
  30.         strSQL = "SELECT * FROM tblBookings WHERE keyUserID=" & rsUsers![keyUserID] & _
  31.             " ORDER BY dteBookedOn ASC;"
  32.         rsBookings.Open strSQL, CurrentProject.Connection, _
  33.             adOpenDynamic, adLockOptimistic
  34.  
  35.         With tskTask
  36.             rsBookings.MoveFirst
  37.             appMSProject.ProjectSummaryInfo _
  38.                 Start:=DateAdd("d", -1, rsBookings![dteBookedOn])
  39.             dteDate = rsBookings![dteBookedOn]
  40.             .Start = dteDate
  41.             rsBookings.MoveLast
  42.             dteDate = DateAdd("d", Nz(rsBookings![intDays], 1) - 1, _
  43.                 rsBookings![dteBookedOn])
  44.             .Finish = dteDate
  45.             rsBookings.MoveFirst
  46.         End With
  47.  
  48.         Do
  49.             dteStart = DateAdd("d", Nz(rsBookings![intDays], 1), _
  50.                 rsBookings![dteBookedOn])
  51.             rsBookings.MoveNext
  52.             If Not rsBookings.EOF Then
  53.                 dteEnd = rsBookings![dteBookedOn]
  54.                 If dteStart < dteEnd Then tskTask.Split dteStart, dteEnd
  55.             Else
  56.                 tskTask.Finish = dteStart
  57.                 Exit Do
  58.             End If
  59.         Loop
  60.  
  61.         rsBookings.Close
  62.         rsUsers.MoveNext
  63.  
  64.     Wend
  65.  
  66.     rsUsers.Close
  67.     appMSProject.Visible = True
  68.  
  69.     Set tskTask = Nothing
  70.     Set rsBookings = Nothing
  71.     Set rsUsers = Nothing
  72.     Set prjProject = Nothing
  73.     Set appMSProject = Nothing
  74.  
  75. End Sub
  76.  
  77.  
Jun 9 '07 #6

P: 49
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
Jun 10 '07 #7

P: 7
Stunning, thanks for the input. Will fiddle.
Jun 14 '07 #8

P: 7
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.
Jun 14 '07 #9

FishVal
Expert 2.5K+
P: 2,653
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.
Jun 14 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.