So in other words you would bring the sequence number into a number
field and then add that to a date field and the working days required
to reach the next date in the process. My Calendar Table is very
similar but I use a calculation that does this all at once and each
time unfortunately.
Such as <If(IsEmpty(ed2a_Comp1Date);"";LookupNext
( Comp1LkUp::TheDate ; Lower))> where "ed2a_Comp1Date" is a
calculated date that is compared to the look up file and gets the
next lower date in sequence (working back to front) if there isn't a
match. So better to have a "number Field" that represents the date
that can then be added (by calc) to the previous date and the working
days to reach the next date.
On Nov 14, 2008, at 4:06 PM, Richard S. Russell wrote:
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.
