Updating calculated fields

  • Thread starter Thread starter RedForeman ©®
  • Start date Start date
R

RedForeman ©®

Date fields seem to be a pain for me... especially datediff

entry fields calculated fields from
left
Admission_Date 1 4 03 LOSAdmitSurg XX
Surgery_Date 1 5 03 LOSSurgDisch XX
Discharge_Date 1 9 03 LOSAdmitDisch XX

XX are calculated fields from the actual dates

Problem is, I'm so fast with data entry, that the XX never gets calculated
while the form is displayed. So at the end of the year, I run queries that
don't even look at those values. But there are other reports that must,MUST
have those values for the record to be validated.

So... options are
run 1 query that updates each XX seperately, resulting in 3 queries
run 1 query that does it all.
open each record to auto update, there are 20000 records, which isn't
feasible..

I need a datediff query, that updates the XX fields, and I'm not sure where
to start, 1 query for all, or 3 queries for each seperately....
 
So... options are
run 1 query that updates each XX seperately, resulting in 3 queries
run 1 query that does it all.
open each record to auto update, there are 20000 records, which isn't
feasible..
Click to expand...

Or the correct option:

Don't store the calculated fields AT ALL. Just store the dates, and -
on the form, at the end of the year, whenever you need them - use
DateDiff to calculate them afresh.
 
Or the correct option:
Don't store the calculated fields AT ALL. Just store the dates, and -
on the form, at the end of the year, whenever you need them - use
DateDiff to calculate them afresh.
Click to expand...

Thanks for that idea,
 
Let me start like this.
Event table has dates:
Admission_Date
Surgery_Date
Discharge_Date

XX table has the stored values of intervals:
LOSAdmitSurg
LOSSurgDisch
LOSAdmitDisch

I need to update XX, I've got datediff statements to give the numbers, how
do i get the numbers back where they belong? They weren't deleted, just not
included when started, then added later. I want to update ALL records.

I need some expression I guess, that will only need to be ran once.... I
just don't know where to start with that expression...
 
Let me start like this.
Event table has dates:
Admission_Date
Surgery_Date
Discharge_Date

XX table has the stored values of intervals:
LOSAdmitSurg
LOSSurgDisch
LOSAdmitDisch

I need to update XX, I've got datediff statements to give the numbers, how
do i get the numbers back where they belong? They weren't deleted, just not
included when started, then added later. I want to update ALL records.

I need some expression I guess, that will only need to be ran once.... I
just don't know where to start with that expression...
Click to expand...

Again... I'd say that your XX table SHOULD NOT EXIST. The values in
LOSAdmitSurg etc. are redundant, and should not be stored anywhere, in
ANY table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you really, really want to store them, then (assuming that Event
table and XX table have a unique field in common), create an Update
query joining the two tables on that field. Update LOSAdmitSurg to

DateDiff("d", [Events].[AdmissionDate], [Events].[SurgeryDate])

and similarly for the other fields. I'd really recommend, though, just
using a Query

SELECT DateDiff("d", [Events].[AdmissionDate], [Events].[SurgeryDate])
AS LOSAdmitSurg, DateDiff("d", [Events].[SurgeryDate],
[Events].[DischargeDate]) AS LOSSurgDischarge, DateDiff("d",
[Events].[AdmissionDate], [Events].[DischargeDate]) AS LOSAdmitDischg;
 
Again... I'd say that your XX table SHOULD NOT EXIST. The values in
LOSAdmitSurg etc. are redundant, and should not be stored anywhere, in
ANY table.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you really, really want to store them, then (assuming that Event
table and XX table have a unique field in common), create an Update
query joining the two tables on that field. Update LOSAdmitSurg to

DateDiff("d", [Events].[AdmissionDate], [Events].[SurgeryDate])

and similarly for the other fields. I'd really recommend, though, just
using a Query

SELECT DateDiff("d", [Events].[AdmissionDate], [Events].[SurgeryDate])
AS LOSAdmitSurg, DateDiff("d", [Events].[SurgeryDate],
[Events].[DischargeDate]) AS LOSSurgDischarge, DateDiff("d",
[Events].[AdmissionDate], [Events].[DischargeDate]) AS LOSAdmitDischg;
Click to expand...

I truly appreciate your answer, but that table is out of my control, it's a
national surgery registry, and I have no control over whether it is or isnt
there. Also, because it's there, it must be utilized, and it's new, so it's
kinda like a pain you learn to deal with...

Thank you again.
 
Back
Top