Re: Complicated...I think

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: John Vinson (jvinson_at_STOP_SPAM.WysardOfInfo.com)
Date: 03/16/04 

Date: Tue, 16 Mar 2004 11:52:17 -0700

On 16 Mar 2004 16:46:52 GMT, angiw@aol.comnospam (Angi) wrote:

>The field being calculated is the amount of time they've been with the company.
> A LOT of people have people have told me not to store this field, but
>EVERYTHING in the db refers to this field, so I thought it was easier to make
>it a stored field rather than run the calculation everytime a form or report
>opens.

The point that everyone (including me) is making is that you can
guarantee one thing:

If you store the number of days that everyone's been with the company
today, YOU KNOW FOR CERTAIN THAT EVERY RECORD IN YOUR DATABASE WILL BE
WRONG TOMORROW.

Sure, it takes some time to calculate a DateDiff from the hire date.
But it takes A LOT MORE time to do that exact same calculation, update
every record in your table, recalculate the indexes on all of the
fields in that table, update the system tables, recompile all the
queries that reference that table, compact the database to recover the
wasted space left by the update query, ...

>Not to mention, I have reports that have to run weekly on who received
>time, lost time, accrued time, and about to lose time (use it or lose it). All
>of this is dependent on whether or not she has everything in the system so I
>can't just calculate it when I run the report because it could be wrong if she
>has outstanding paperwork.

But if they have outstanding paperwork it will STILL be wrong, whether
you're storing this value or not storing the value.

> We're talking about a user that needs to be able to
>answer a yes or no question and push a button. All thought processes need to
>be on the db, not on the user. If you think there is a better way to do this,
>please tell me, I'm all ears! This is driving me crazy! Thanks!

Store the hiredate and calculate the amount of time they've been with
the company when you need it, using a calculated field in a Query.
Then you know it will be accurate as of the instant the query is run;
the operator will not need to do ANYTHING; you won't have to wonder
whether or not the update query has been run or not.

Your Forms and Reports should still work fine; just change their
Recordsource to a query in which you calculate the time worked rather
than referencing the time-worked field.

                  John W. Vinson[MVP]
    Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

Quantcast