Date calculations

[Michael Neal]

I have 3 fileds as follows:-

Field 1 Named "Priority" contains text Values of
either "Critical", "Priority", "Urgent" or "Normal".
these 4 values represent time frames for action of work.
i.e. Critical = 8 hours
Priority = 24 Hours
Urgent = 7 Days
Normal = 14 days

Field 2 Named "Date 1" which = today's date.

Field 3 Named "Due date"

I need to have a date automatically entered into "Due
Date" based on value of field value of "Priority" + "date
1"

So that the calculation looks at the text entered in
the "Priority" Field and equates each name as a value
such as Urgent beign 7 then adds that value of 7 to
todays date. resulting as follws:-

Priority e.g. Urgent, which in turns equals a value of 7
it then adds 7 to todays date i.e 1st Oct 2003 and enters
a date of 8th Oct 2003 into the DUE Date Field.

If anyone can help with this calculation i would be for
ever in your debt.

Sincerely
Michael Neal
Morwin pty Ltd
Melbourne Australia

 

[Jeff Boyce]

Michael

There's good news (and bad...).

You don't need to store the DueDate, as it is easily calculated from the
other data you have. One of the guiding principles (or at least,
considerations) of relational database design is to avoid storing calculated
values. There are valid exceptions, but this isn't one.

Now the bad news ... you still have to DO the calculation. And I'm going to
suggest that you plan for the future and "bite the bullet" now (sorry, an
Americanism there). Try the following:

Create a lookup table with your priorities:
tlkpPriority
PriorityID (I'd suggest Autonumber)
PriorityText (this field holds your "Critical", "Priority", "Urgent"
....)
TimeFrame (I'd suggest showing this in hours -- 8, 24, 168, 336)

Next, you have some "thing" you didn't describe in your post that has a
DateBegun (it isn't the priority that has a date begun, it's "something",
right -- ?maybe a reported outage). By the way, it's safer to avoid naming
a field "Date", as this is a reserved word in Access and will only end up
confusing both you and Access. Here's a "thing" table:
tblThing
ThingID (?Autonumber)
ThingDescription
DateBegun
PriorityID (the value that goes here comes from the tlkpPriority
table, as a foreign key)
... (other facts about this Thing)

Finally, build a query against your tblThing, joined to tlkpPriority. In
the query, include the relevant fields from tblThing, and create a new
expression with something like:

DueDate: DateAdd('h',[DateBegun], [TimeFrame])

(actual syntax may vary -- confirm by checking Access HELP on the DateAdd()
function)

Now, anytime you want to know the DueDate for a "thing", use the query, not
the table directly.

Why would you bother to go to all this work?

Because the amount of work you'd need to do to bullet-proof your design if
you didn't take these steps would be much greater! For instance, you'd need
to build the routines that would guarantee your DueDate and DateBegun dates
were kept in sync if one of them changed. Like, say, when someone edits a
DateBegun because it was entered in error.

And what if your Service Level Agreement changed, and you wanted to handle
every priority setting in half the time? In the design above, you could
just edit the relevant tlkpPriority fields. In your design, you'd have to
go back through your entire database, looking for every place you hard-coded
the calculation.

Good luck!

Jeff Boyce
<Access MVP>