Calculated field in a table

[Logo]

I've got a table with 10 of 15 fields calculated. I enter data into the
table via a form, which does the calculations. The form works fine, but for
some reason the last 4 calculated fields don't get entered into the table.

Should I even have calculated fields in a table, or should they primarily be
used in queries and on reports?

If ok to put in tables, any idea why the last few calculated fields aren't
being recorded? Then 1 out of maybe 15 records the calculated fields do get
filled! The same data is input for every record. TIA

 

[John W. Vinson]

Should I even have calculated fields in a table, or should they primarily be
used in queries and on reports?

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.

 

[å¼ å°å»º]

I've got a table with 10 of 15 fields calculated. I enter data into the
table via a form, which does the calculations. The form works fine, but
for
some reason the last 4 calculated fields don't get entered into the table.

Should I even have calculated fields in a table, or should they primarily
be
used in queries and on reports?

If ok to put in tables, any idea why the last few calculated fields aren't
being recorded? Then 1 out of maybe 15 records the calculated fields do
get
filled! The same data is input for every record. TIA

 

[Logo]

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.

Awesome, thank you very much!

 

[a a r o n . k e m p f]

Calculated fields aren't allowed in Access until Access 2010.

it's been a great feature in Access Data Projects for the past 11
years though, I use it on a daily basis for sure.

Being able to add 'CS' prefix to certain keys/fields, silly stuff like
that? It just makes it so much easier to deal with database logic of
_ANY_ complexity.

It's always amazed me - two things:
a) Jet crybabies say it's a bad idea to do (no it's not, it saves
time, you can either persist or re-calc the calculation)
b) that Jet didn't offer this a long time ago.

Being able to index a computed field in Access Data Projects is one of
my favorite options

What is it?

ALTER TABLE tblEmployees
ADD ULASTNAME = UPPER(LastName)

Stuff like that is best done in the database table itself- instead of
in 100 different places. Perhaps it's formatting a phone number, etc.
Do it in one place, or in 20?

I use this all the time to concatenate Addresses together.. at first
my workers like 'wtf are you doing' but now it's one of the best
things in the world-- because we have _ONE_ definition for a
calculation, instead of 20.

PS - being able to index computed fields is a _BIG_ Deal

ALTER TABLE tblEmployees
ADD CSLASTNAME = CHECKSUM(LastName)

CREATE INDEX tblEmployees_CSLASTNAME
om dbo.tblEmployees (CSLASTNAME)


Now, it's a ton faster to search for the hashed data instead of the
big slow varchar field

select *
From tblEmployees
Where CSLASTNAME = CHECKSUM('KEMPF')

compare it to this statement using Query Analyzer or SQL Management
Studio and you'll be able to quantify the performance difference

select *
From tblEmployees
Where LASTNAME = 'KEMPF'

 

[De Jager]

I've got a table with 10 of 15 fields calculated. I enter data into the
table via a form, which does the calculations. The form works fine, but
for
some reason the last 4 calculated fields don't get entered into the table.

Should I even have calculated fields in a table, or should they primarily
be
used in queries and on reports?

If ok to put in tables, any idea why the last few calculated fields aren't
being recorded? Then 1 out of maybe 15 records the calculated fields do
get
filled! The same data is input for every record. TIA

 

[joelgeraldine]

::::vvvvvvvv;