On 2008 Nov 14, at 14:50, Peter Kilcoyne wrote:
First of all let me thank all that responded to my "I'm at a lost"
post, "Database Sluggish Post" etc. Posts. I tried your ideas but
I'm afraid that I corrupted the database when I forced quit after
realizing the calc change was going to take 2 days to make (maybe a
slight exaggeration). But now I am reconciled to the fact that the
only salvation I have is to rebuild.
So to that end I'd like to ask how others might go about setting up
a scheduling database that requires work days and a special
consideration for holidays? Since that is the biggest portion of
the database. I currently use a calendar table that doesn't have
weekends or the special holidays that are in academia listed. By
using "Lookup next" calcs and straight Look up fields the correct
date is entered and then adding the appropriate number of work
days to the calc I can go to the next date. This goes on for 11
date fields ( maybe a clue to the lengthly recalculation time huh?).
Is there a better way? I'm going to start Monday morning after a
weekend of washing my sorrows away.
Again I can't thank you folks enough for your advise.
Peter
I have, in the past, been seduced by the siren allure of
calculations. They seem to simple, so elegant, so easy to implement.
Alas, it has never worked out that way.
I have instead always ended up resorting to the brute-force method of
having a look-up table that lists every date for the next decade
(particularly Feb. 29 in leap years), with each date categorized as
either a work day, weekend day, or holiday. (In an academic setting,
other categories, such as "exam day" or "study day", are also
useful.) Each work day also has a sequence number, beginning with 1
back on 2001 Jan. 2 (Jan. 1 having been a holiday), and I use lookups
to import these sequence numbers into the scheduling database. (A
truly normalized database wouldn't do lookups. It would rely on a
relationship to access the sequence numbers whenever they're needed.
However, this can, over time, produce performance hits. This is one
of the cases where I justify deviating from true normalization for
practical reasons, especially since these lookups are highly reliable.)
Once the work-day sequence numbers are safely imported into the
Scheduling database, you can perform normal arithmetic on them to
find timespans.
