Building a chart based in dates

Building a chart based in dates

Post by GR » Sat, 01 Nov 2003 04:48:24


I have a table which has a field for offer numbers. for
each offer there are several resources that will be used
for that order during a specific period of time. So there
are two fields "From" and "To" which are date field and is
the period in which those resources will be in used. So if
offer A use resources x,y,z during a period T specified by
the fields "from" and "to" then if offer B needs resources
x,y,z offer B should be programmed in a period T1
different from period T of offer A (because offer A will
be using the resources) if offer C needs resources b,c,d
it can be programmed in period T because there's no
resource overlapping.
What i've been asked to do is a form with a chart that
shows a graph with all the offers and the dates for each
offers so it can be easily view if there's a resource
overlapping and can also be easily programmed new dates
for new comming offers avoiding overlapping.
all the data is provided by one table.
How can i make this chart? I have been trying with the
chart wizard but i just don't know how to do it.

Example fields:

OfferNo. From To
100001 22/01/2003 31/01/2003
100002 24/01/2003 26/01/2003
100003 10/01/2003 02/02/2003


Building a chart based in dates

Post by Michel Wal » Wed, 12 Nov 2003 21:48:43


It is easier to find make the SQL statement that find overlapping that
to make the charts.

There is no overlapping at all between ( s1, e1) and (s2, e2) , note I
used s for starting and e for ending, if:

s1>e2 OR e1<s2

There is overlapping in the negation:

NOT ( s1 > e2 OR e1 < s2 )

which is also

s1 <= e2 AND e1>= s2

note that the equality is either a boundary effect, either a full day
overlapping if e2 is a date and the ending data itself is inclusive, rather
that "up to, exclusively. In other words, I leave to you the care to know if
it is >= or just >, and <= or just <, in your situation.

So, we can get intervals with overlapping periods (in part or in full):

SELECT a.ressourceID, a.jobID, a.starting, a.ending, b.JobID, b.starting,
FROM myTable As a INNER JOIN my Table As b
ON a.ressourceID = b.ressourceID
AND a.JobID <> b.JobID
AND a.starting <= b.endint AND a.ending >= b.starting
ORDER BY a.ressourceID, a.starting

That does not solve your problem to "show" when resources are in use. To
do that with versatility, you would the possibility to "draw" lines (or
rectangles) on a control. Unfortunately, there is nothing in Access, right
out of the box, that allows you to do that easily. In fact, you may find an
ImageClass that can help you starting in that direction. Since dates are, in
fact, number of days (since the 30th December 1899), you can place a time
scale horizontally, and, vertically, have your resources, a, b, c, on
different "lines". You then draw a line segment accordingly to the starting
date and ending date. Once you have drawn all the lines, you know where the
resources you require are available, at the same time.

Maybe even that is easier to do in SQL?

Assume a table DateOpenToWork with one field, dWork, a (vertical) list of
dates open to work. Assume a table RequiredRessources, one field,
RessourceID, a vertical list of the resource we are looking for
availability. We also have, as previously, a table Jobs, with the JobID, the
ressourceID, the starting and ending time fields (on record per job, per


FROM DateOpenToWork INNER JOIN (Jobs INNER JOIN RequiredRessources ON
Jobs.RessourceID=RequiredRessources) ON DateOpenToWork.dWork BETWEEN
(Jobs.Starting AND Jobs.Ending)

supply the dWork "in use". The days "not in use", for witch some resource
is free, would be:

FROM DateOpenToWork LEFT JOIN (Jobs INNER JOIN RequiredRessources ON
Jobs.RessourceID=RequiredRessources) ON DateOpenToWork.dWork BETWEEN
(Jobs.Starting AND Jobs.Ending)
WHERE Jobs.JobsID Is Null

Having the list of days where all the resources are all free, it is just a
matter to find N consecutive days. Up to now, we have assumed that the table
DateOpenToWork has only one field, now, assume there is a second field, the
"rank" of the day, ie, due to weekend, and holidays, the days are not
necessary consecutive, so, we have something like:

dWork, rank
January 2 1
January 5 2
January 6 3

assuming the second of January is not an holiday, and that the third and
fourth of Jan, that year, were a Saturday and an Sunday. The "rank" is just
a convenient "continuous number". Back to our query,