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
|
|||
|
|||
table design - calculated field
I have a field in my Zip Code table called Current_Vendor_Count. This value
is a calculation - it is calculated on a form. Is there a way I can store the calculation at the table level? Am I wrong to want to even do this? Are values that are calculated on the fly in a form, generally not stored in the within the table? The following is the calculation: Current_Vendor_Count = DCount("*", "tbl_Vendor", "(tbl_Zip_Code.Zip_Code = tbl_Vendor.P_Zip_Code) AND (tbl_Zip_Code.Local_Agency_Code = tbl_Vendor.Local_Agency_Code) AND [Status_Code] = 'Active' AND [Peer_Group_Code] 11 ") |
#2
|
|||
|
|||
table design - calculated field
Yes, storing calculated values in the database is not a good idea. In
the first place it violates a Relational Rule. That's just a way of quoting Authority. :-) There are very good reasons why you shouldn't. You calculate in one place but the result would then go into a table. That table can be viewed and massaged from more than one place If you can see it you can change it. You can see the elements which were elements in the calculation they can be changed without performing the calculation. Sooner or later, trouble arises silently. You get the idea. The practice is to perform the calculation any time you need to display the result.It requires significantly less storage to manage the code for the calculation than it does to grow every record in your table(s) by storing the results. Even Access databases can get into the millions of records. HTH -- -Larry- -- "Lori" wrote in message ... I have a field in my Zip Code table called Current_Vendor_Count. This value is a calculation - it is calculated on a form. Is there a way I can store the calculation at the table level? Am I wrong to want to even do this? Are values that are calculated on the fly in a form, generally not stored in the within the table? The following is the calculation: Current_Vendor_Count = DCount("*", "tbl_Vendor", "(tbl_Zip_Code.Zip_Code = tbl_Vendor.P_Zip_Code) AND (tbl_Zip_Code.Local_Agency_Code = tbl_Vendor.Local_Agency_Code) AND [Status_Code] = 'Active' AND [Peer_Group_Code] 11 ") |
#3
|
|||
|
|||
table design - calculated field
Larry Daugherty wrote: The practice is to perform the calculation any time you need to display the result.It requires significantly less storage to manage the code for the calculation than it does to grow every record in your table(s) by storing the results. I think the measure should be "when the cost of the calculation is higher than the cost of a simple read": http://www.dbazine.com/ofinterest/oi-articles/celko4 it violates a Relational Rule. That's just a way of quoting Authority. :-) I'll quote another: "You are not supposed to put a calculated column in a table in a pure SQL database. And as the guardian of pure SQL, I should oppose this practice. Too bad the real world is not as nice as the theoretical world." Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Add New Field to DB | Karen | Database Design | 7 | October 19th, 2005 08:03 PM |
Multiple Options Group | Patty Stoddard | Using Forms | 19 | August 4th, 2005 02:30 PM |
Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
Design help, please | SillySally | Using Forms | 27 | March 6th, 2005 04:11 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |