Best approach to show event length and display it as blocks of time (1 Viewer)

5hadow

Member
Local time
Today, 13:14
Joined
Apr 26, 2021
Messages
89
Hello everyone,

I'm really stuck on this one. I have a database which tracks status of fleet, and hours spent on each "Event". I want to use the data to show a chart, or a planner in a "tape" fashion and show each event in blocks of time. It's kind of hard to explain so I will include screenshots.

Here is my main screen:
1654135599549.png


When you "Update Status", it opens a new event, adds a start timestamp and closes previous event, also ads closed timestamp.

Below is a sample of data table:
1654135801121.png



With data above, I'd like to show a time block (possibly with some details such as "Cause" field) and plot the hours on to the chart portion of the main screen:

1654135983615.png


Notice the yellow box is the current day. It shows the current day with all events that belong to this day. Some events may begin on a previous day, in that case all blocks would be shaded and if the event didn't end, the block would terminate at the current time. I've manually altered the chart to show what I mean:

1654136697940.png


What in the world do I need to do to make this work?

By the way, I've included a .zip file with database if anyone wants to have a look.
Thanks!
 

Attachments

  • Serviceability Tracker.zip
    797.8 KB · Views: 135

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 12:14
Joined
Feb 28, 2001
Messages
26,999
Assuming you don't want to dynamically generate a bar-chart via the MS Graph function (and I wouldn't do that because it would be a pain in the patootie besides not being very flexible), the problem you run into is tedious coding. You COULD use a continuous subform that can be easily scrolled up or down.

The nastiest part is that you would have one very tedious time composing the conditional formatting for a row of 25 slots - 1 for the event number (that doesn't need conditional formatting because its format would probably be constant) and 24 blocks for the 24 hours of time slots. I'm assuming you only allocate time in units of hours for this...

Your header could be just constant labels for the 24 time slots and a text box for the date. You would have two "condition" formulas for each block, and each block would be a SLIGHTLY different formula because each block represents a different hour of the day.

Since you are only doing two colors, your conditions would be that for green, you want the block's number to be less than the table's rounded start OR greater than the rounded end. For the red color, you want the block's number to be greater or equal to the table's rounded start and less than or equal to the rounded end. So I guess that the rounded start and end would be that you use a DatePart to pick out the hour of the day for start and end. Which means you might wish to drive this with a query rather than the raw table, because that way you can do some of the DatePart stuff in the query and not have to store it.

Hope that made sense.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:14
Joined
Feb 19, 2002
Messages
42,971
Your query needs to select rows where the date(s) you are looking for is between fldDateStart and fldDateEnd. Then you need to populate each hour.

Select IIf(1 >= Hour(fldTimeStart) and 1 <= Hour(fldTimeEnd), true, false) as Hour01, IIf(2 >= Hour(fldTimeStart) and 2 <= Hour(fldTimeEnd), True, False) as Hour02, ....

Then the conditional formatting would color the field based on whether it was true or false.

You can probably come up with something more elegant if you make a function or you can use the curmudgeon approach above. As Doc said, this is tedious rather than difficult once you understand how to do it.
 

CarlettoFed

Member
Local time
Today, 18:14
Joined
Jun 10, 2020
Messages
119
In the attached file you will find an example of how it could be done.

However, the first thing to do is learn how to manage the properties of the various fields of the tables and in particular:
- Size (if the type is text, don't always leave 255 set as you almost always won't need all those fonts to avoid wasting disk resources, see fldCause field)
- Required (if set to Yes it does not allow you to save the record if the requested data has not been entered, see fldCause field)
- Allow zero length (to be set to No if data is always required, see fldCause field)
- indexed (set to "Yes (duplicates are not allowed)" when you want a value not to be entered more than once in a field and therefore to be unique, see fldCause field).
- index management if it is necessary to use two or more fields, in addition to the primary key, to avoid duplication of records.
 

Attachments

  • ServiceabilityTrackerNew.zip
    154.1 KB · Views: 140

Users who are viewing this thread

Top Bottom