If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculated field in a table
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 |
#2
|
|||
|
|||
Calculated field in a table
On Tue, 9 Mar 2010 22:16:04 -0800, Logo
wrote: 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. -- John W. Vinson [MVP] |
#3
|
|||
|
|||
Calculated field in a table
"John W. Vinson" wrote: On Tue, 9 Mar 2010 22:16:04 -0800, Logo wrote: 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. -- John W. Vinson [MVP] . Awesome, thank you very much! -- Logo |
#4
|
|||
|
|||
Calculated field in a table
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' On Mar 9, 10:16*pm, Logo wrote: 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 |
#5
|
|||
|
|||
Calculated field in a table
"Logo" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|