Question Conceptual Design and methodoligy

[Christopher770]

I need to create a visual representation of time to illustrate gaps of time between two segments of time.

File:
Schedule ID
Depart Time
Depart Day of week
Arrival Time
Arrival Day of the week.
Equipment ID

Example of desired result: Schedule A Departs at 08:00am and arrives at 17:00pm with equipment number 1. Schedule A does not leave again until the following day at 08:00. This means Equipment #1 is available for use between 17:00pm until 08:00am. There could be 100's of day/time combinations and equipment that would need to be considered.

The net result is to visually display to the user by equipment number the gaps of time between arrival and departure. Something similar to the attached (very crude representation)

Hopefully what I described made sense.

I'm asking for suggestions to create the process for displaying the results. While I'm thinking gantt chart for the the end users to visually understand the arrival/departures and availability, I'm open to all suggestions.

Thank you
Christopher

 

[DavidAtWork]

I'm not sure Access is the right/best tool for your visual output, it could certainly manage data representing the timeslots taken by each schedule for each piece of equipment, but to turn that data into the desired visual output will be tedious as I see nothing better than creating a report which will comprise of 48 text boxes strung together to represent each half hour time slot in a 24 hour period. To then give a visual formatted picture for each piece of equipment, each text box would have to be colour formatted according to availability and that would be determined by inspecting the data for each schedule & piece of equipment, not straightforward by any means.

David

 

[spikepl]

I too do not think Access is the best tool for displaying this type of info. You can buy an add-in to deal with Gantt-charts, but that would seem overkill.

Why not use Excel ? You can manage your data in Access and from there write it in an Exce-instancel, to obtain a result akin to the one you showed in your picture. You can get a similar result in Access but that will be a lot of pain to make.

 

[stopher]

This can be done fairly easily in a report using a single rectangle control (box1) and four other controls (hidden):

• DepartTime in Detail
• ArrivalTime in Detail section
• groupRecNum (set to =1 and Running Sum set to Over Group) in Detail section
• EquipGroupCount (set to =Count(*)) in EquipmentID Header section

And the code...

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    
    Me.MoveLayout = (Me.groupRecNum = Me.EquipGroupCount)

    Me.Box1.BackColor = Me.equipColour
    Me.Box1.Left = (Me.DepartTime * 24) * 450
    Me.Box1.Width = (Me.ArrivalTime - Me.DepartTime) * 24 * 450
    
End Sub

The rectangle is sized and positioned using the departure and arrival times.

In order to get multiple rectangles (equipment records) on the same line, MoveLayout is set to false unless the last record in the equipment group is reached (hence the record counter and the count field).

Colours are done from a table join.

 

[Christopher770]

Wow!!! Thank you for that! I hope you didn't create this just for me, however if you did, I'm humbled by your generosity. Visually this is what the users want to see. I'll have to tinker with the math for to account for midnight cross-over. But I believe I see what you have going on here. Thank you again so much.