Complicated...I think

  • Thread starter Thread starter Angi
  • Start date Start date
A

Angi

If I make this too confusing, I apologize in advance. I'll try my best.

I have a database that runs multiple updates on all the records. The user
wants to prompted for the update in case she has info to enter first. That
part is easy. She also wants to know how many days it has been since she's
updated it and how many records should be updated between then and today. Then
I need a cmd btn to list the records so she can see if it will affect anything
first.

Now, I have a yes/no msgbox that prompts her for whether or not to update. If
she says no, I have a form that will open and display the last date she
updated, the time in between and how many records should be updated. Then I'll
add a cmd btn to view the records. Sounds good, right?? Yeah, well how do I
do that? I've tried using a LastAccessed field with Date() as it's default.
That doesn't work because obviously, everytime it opens, it updates. DateDiff
I can handle. I think I can handle showing which records using the Between
statement, but how do I count the records that should've been updated? TIA!
You've all been so much help to me in the past!
 
Hi Angi,

What are the rules that determine whether any given record needs to be
updated? Unless you and the user are absolutely clear on this (e.g. "if
it hasn't been viewed in the last three months it's a candidate for
updating") you can't proceed.
 
John,
The criteria for when an update occurs has nothing to do when it was last
viewed. Everything updates when the database opens, unless she says not to.
Everything updates according to an employee's hire date, which can be anytime,
so the module runs everyday and only updates those that need it. Her problem
is that some time may have been taken between the last time she opened it and
now for someone who is supposed to update and that will affect their available
time. IE: Someone loses sick time because they didn't use it, when they
actually did, but it wasn't entered yet.

Thanks,
Ang
 
Everything updates when the database opens, unless she says not to.
Everything updates according to an employee's hire date, which can be anytime,
so the module runs everyday and only updates those that need it.
Click to expand...

WHOA.

It certainly sounds like you're storing calculated fields in your
Table. This is *very* inefficient and leads to exactly the kind of
problems you're having - "has the table been updated or not, and
when"?

Rather than storing the calculated values in the table, and updating
them everyday, store only the base data - the hire date or whatever -
and use Queries to calculate the information on the fly.

Just what is being calculated and updated? Is there any good reason to
store the value, given that (apparently) it can be calculated as
needed with your module, and that you can use a Query as the
recordsource of a form or report?
 
John,
Just what is being calculated and updated? Is there any good reason to
store the value, given that (apparently) it can be calculated as
needed with your module, and that you can use a Query as the
recordsource of a form or report?
Click to expand...

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. 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. 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!
Angi
 
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.
Click to expand...

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.
Click to expand...

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!
Click to expand...

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,
I guess I just don't see if the difference since it updates when it opens, so
the number is always right, but, ok, I'll do what you said as far as the time
worked. Now...it still doesn't solve my initial problem of my first post since
the system MUST update:
I have a database that runs multiple updates on all the records. The user
Click to expand...
wants to prompted for the update in case she has info to enter first. That
part is easy. She also wants to know how many days it has been since she's
updated it and how many records should be updated between then and today. Then
I need a cmd btn to list the records so she can see if it will affect anything
first.

Now, I have a yes/no msgbox that prompts her for whether or not to update. If
she says no, I have a form that will open and display the last date she
updated, the time in between and how many records should be updated. Then I'll
add a cmd btn to view the records. Sounds good, right?? Yeah, well how do I
do that? I've tried using a LastAccessed field with Date() as it's default.
That doesn't work because obviously, everytime it opens, it updates. DateDiff
I can handle. I think I can handle showing which records using the Between
statement, but how do I count the records that should've been updated?
 
Back
Top