Time Series Timelines

[mbreu996]

This form is a utility to help the user import time series data to the database. The only thing I'm stuck on is how to make a nice graphic for the user that will help them quickly know if they have time series overlaps.

Table1
Preexisting time series data from several different sites. Already imported into the database.

Table2
New or possibly redundant time series data. Could be earlier or later dates than Table1.

The file being imported could have 1 or more sites and ranges of times. How can I make a graphic that looks like a timeline that will show a blue bar for the existing data, green for new data that doesn't overlap, and red for new data that overlaps?

Site1
New--|_______|------------

Existing -----------|_______|

Site2
New--|______|X|______|----

Existing ------|_|------------

for 1 or more sites...

 

[mbreu996]

bump ^
anyone have any suggestions?

 

[ChrisO]

I’m not surprised it needed bumping.

For something like a Gantt chart, which is what I think it is, one problem will be the number of records involved.
I’m thinking along the lines of the Site Names down the left side of the Form with the bar graphs on the same line.
At the top of the Form should be a date/time scale which covers the range from minimum date/time to maximum data/time of the entire set of data. The resolution of that date/time scale will depend on the date/time range so that only around 20 to 40 date/time markers are shown. The precise date/time range of each bar can be shown on mouse over.

So, the first thing to ask is if you can create a query which will return one only row for each Site Name that also includes the start and end date/times for each bar for that site?

Next thing to ask is how many sites are involved?

 

[mbreu996]

Thanks for the response. The start/end date time for each site query is no problem for me to make. There could be any number of sites but likely range would be about 1-10. The resolution of the timescale can be very course.

 

[ChrisO]

Okay, we have a start.

I think I will need the following…

1. The tables.
2. The query you write.
3. The screen resolution required (1024x768 or preferably 1280x1024). I may be able to do it at any reasonable screen resolution but it just makes it more difficult.
4. Visual confirmation of the result. I’ve attached a screen dump image of something similar if that helps.

 

[mbreu996]

Thanks a million. I like the site 3 non-overlapping also. I will prepare all that stuff and post later today.

 

[ChrisO]

Okay but there are a few other things…

This may take a while (a few days) and I may need to ask further questions. To help that process it would be handy to know your approximate time zone so I can ask those questions at an appropriate time.

It would also help if you can keep your email open because you may get pinged by this site with those questions, if any.

Also, any data posted must be in Access 2003 or earlier format, I don’t have Access 2007.

I was intending to do this in Access 2003 but using Access 2000 file format, is that okay?