buddythebest
Registered User.
- Local time
- Today, 10:06
- Joined
- Mar 31, 2006
- Messages
- 32
In an orders table, i want to have a total value calculated... in the table... i was only able to do that in a query... but is it possible to do so in a table?
Is it possible to have a calculated field in a table?
- Thread starter buddythebest
- Start date
R
Rich
Guest
It's not recommended to store calculated values, use the Query method you already have
buddythebest
Registered User.
- Local time
- Today, 10:06
- Joined
- Mar 31, 2006
- Messages
- 32
ok thanks.... that means that it is not possible to do so.... or just tht it's not recommended... bcuz for me it would be much better if i could have it in the table
- Local time
- Today, 03:06
- Joined
- Feb 19, 2002
- Messages
- 47,594
It isn't a matter of its being better for you. Anything you can program is possible so yes it is possible to store a calculated value in a table. Will this be easier for you? Unlikely. Is it a good idea? Absolutely not. Why? Search for normalization and database design topics for explainations in detail.
- Local time
- Today, 02:06
- Joined
- Feb 28, 2001
- Messages
- 30,499
Buddy, I'll add a comment.
When you ask to store a calculated field in a table, you are thinking in Excel terms. Excel glosses over the differences between data and formulas by the way they are displayed. Excel hides the machinations of formulas. And until you run into a case where you needed a formula instead of raw data, Access and Excel DO have similar appearances in table view.
BUT - here is the key to your thinking. In Access, tables are not really static but they are not dynamic in the same sense that Excel worksheets are dynamic. The idea with Access is that you are keeping records in a table. Once written, they can certainly be updated, but anything that is a computed field in one record implies computed fields in other records. That means a lot of computation to keep the computed field up to date. For a small DB (or any spreadsheet), this is usually not a big load. If you are heading towards an inventory, however, you are heading for a big headache if you keep this mind set.
Here is your equivalent of an aspirin. Anything you need to compute, you compute from an appropriate query. Now at first this sounds strange, but here is why it isn't. Almost anything you could base on a table, you can base on a query. A query looks almost exactly like a table. Why? Because both tables and queries, when opened, produce recordsets. A table produces a table-type recordset. (Pretty much all it can do.) A query has more choices. But to other Access elements, most of the time a recordset is a recordset is a recordset.
Forms and reports? No problem. You can drive either from a query. All they usually want is a recordset.
Another query? No problem. Access allows you to layer queries. In fact, if you are doing more than a two-way JOIN, you probably MUST use multiple layers of queries. Each query just produces a filtered or re-ordered recordset.
And in queries, you can have any formulas you want.
The ONLY big difference is when you have aggregate queries, for which one record from the query doesn't represent a single record from the underlying tables. In that case, you might have a non-updatable query. But if the query you built is one-to-one with a record or a JOINed record, it probably can be updated without incident.
When you ask to store a calculated field in a table, you are thinking in Excel terms. Excel glosses over the differences between data and formulas by the way they are displayed. Excel hides the machinations of formulas. And until you run into a case where you needed a formula instead of raw data, Access and Excel DO have similar appearances in table view.
BUT - here is the key to your thinking. In Access, tables are not really static but they are not dynamic in the same sense that Excel worksheets are dynamic. The idea with Access is that you are keeping records in a table. Once written, they can certainly be updated, but anything that is a computed field in one record implies computed fields in other records. That means a lot of computation to keep the computed field up to date. For a small DB (or any spreadsheet), this is usually not a big load. If you are heading towards an inventory, however, you are heading for a big headache if you keep this mind set.
Here is your equivalent of an aspirin. Anything you need to compute, you compute from an appropriate query. Now at first this sounds strange, but here is why it isn't. Almost anything you could base on a table, you can base on a query. A query looks almost exactly like a table. Why? Because both tables and queries, when opened, produce recordsets. A table produces a table-type recordset. (Pretty much all it can do.) A query has more choices. But to other Access elements, most of the time a recordset is a recordset is a recordset.
Forms and reports? No problem. You can drive either from a query. All they usually want is a recordset.
Another query? No problem. Access allows you to layer queries. In fact, if you are doing more than a two-way JOIN, you probably MUST use multiple layers of queries. Each query just produces a filtered or re-ordered recordset.
And in queries, you can have any formulas you want.
The ONLY big difference is when you have aggregate queries, for which one record from the query doesn't represent a single record from the underlying tables. In that case, you might have a non-updatable query. But if the query you built is one-to-one with a record or a JOINed record, it probably can be updated without incident.
