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
|
|||
|
|||
How do you mulitply in a field?
I created a table with two fields named "projectestimate" and "inspectionfee".
The inspection fee is 5% of the project estimate. I want the "inspectionfee" field to automically calculate the amount. How do I do that? |
#2
|
|||
|
|||
How do you mulitply in a field?
On Wed, 15 Aug 2007 13:34:01 -0700, wazabbbi wrote:
I created a table with two fields named "projectestimate" and "inspectionfee". The inspection fee is 5% of the project estimate. I want the "inspectionfee" field to automically calculate the amount. How do I do that? In a table? You don't. Access tables are for storing data, not for data manipulation or display. If you store the [ProjectEstimate] and [InspectionRate] it's easy enough to calculate the actual fee in a Query: InspectionFee:[ProjectEstimate] * [InspectionRate] Or you can do the calculation directly on a form or report, using an unbound text control: = [ProjectEstimate] * [InspectionRate] Either way, the resulting value should not be saved in any table. -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail |
#3
|
|||
|
|||
How do you mulitply in a field?
On 15 Aug, 22:05, fredg wrote:
I created a table with two fields named "projectestimate" and "inspectionfee". The inspection fee is 5% of the project estimate. I want the "inspectionfee" field to automically calculate the amount. How do I do that? In a table? You don't. Access tables are for storing data, not for data manipulation or display. you can do the calculation directly on a form or report, using an unbound text control: = [ProjectEstimate] * [InspectionRate] the resulting value should not be saved in any table. In this simple case I agree: there can be no good reason for storing the result. If you store the [ProjectEstimate] and [InspectionRate] it's easy enough to calculate the actual fee in a Query: InspectionFee:[ProjectEstimate] * [InspectionRate] Not sure I agree anymore. Did you mean to say 'query' (lowercase)? If you saved your query with the calculated column as a stored Query (Title Case) object then this would become a virtual table. You would seem to be saying that denormalization in a base table is wrong but fine in a virtual table. That sounds contradictory. For me, this is one of the differences between a VIEW and a PROCEDURE i.e. the resultset of a PROC doesn't need to be normalized. The trouble is, how do you get the Access/Jet engine to see your PROC as a PROC e.g. this in Northwind (ANSI-92 Query Mode SQL syntax): CREATE PROCEDURE GetOrderProcessIntervals AS SELECT OrderID, OrderDate, ShippedDate, DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days FROM Orders; actually creates a VIEW! i.e. although it can be EXECUTED: EXECUTE GetOrderProcessIntervals; it can also be queried as a table: SELECT * FROM GetOrderProcessIntervals; and we now have a denormalized table. The only workaround I can think of is to supply a useless parameter value e.g. CREATE PROCEDURE GetOrderProcessIntervals ( this_not_used INTEGER = 0 ) AS SELECT OrderID, OrderDate, ShippedDate, DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days FROM Orders; Now SELECT * FROM GetOrderProcessIntervals; fails with a 'missing parameter' error. So the choice seems to be to ask users to ignore daft parameter or be a little less strict on normalization rules. Jamie. -- |
#4
|
|||
|
|||
How do you mulitply in a field?
Jamie
I'm curious why the same admonition about rarely needing to store calculated values in a table would have to be applied to views, i.e., your "virtual tables"? Do you have a reference so I can read up more on this? Regards Jeff Boyce Microsoft Office/Access MVP "Jamie Collins" wrote in message oups.com... On 15 Aug, 22:05, fredg wrote: I created a table with two fields named "projectestimate" and "inspectionfee". The inspection fee is 5% of the project estimate. I want the "inspectionfee" field to automically calculate the amount. How do I do that? In a table? You don't. Access tables are for storing data, not for data manipulation or display. you can do the calculation directly on a form or report, using an unbound text control: = [ProjectEstimate] * [InspectionRate] the resulting value should not be saved in any table. In this simple case I agree: there can be no good reason for storing the result. If you store the [ProjectEstimate] and [InspectionRate] it's easy enough to calculate the actual fee in a Query: InspectionFee:[ProjectEstimate] * [InspectionRate] Not sure I agree anymore. Did you mean to say 'query' (lowercase)? If you saved your query with the calculated column as a stored Query (Title Case) object then this would become a virtual table. You would seem to be saying that denormalization in a base table is wrong but fine in a virtual table. That sounds contradictory. For me, this is one of the differences between a VIEW and a PROCEDURE i.e. the resultset of a PROC doesn't need to be normalized. The trouble is, how do you get the Access/Jet engine to see your PROC as a PROC e.g. this in Northwind (ANSI-92 Query Mode SQL syntax): CREATE PROCEDURE GetOrderProcessIntervals AS SELECT OrderID, OrderDate, ShippedDate, DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days FROM Orders; actually creates a VIEW! i.e. although it can be EXECUTED: EXECUTE GetOrderProcessIntervals; it can also be queried as a table: SELECT * FROM GetOrderProcessIntervals; and we now have a denormalized table. The only workaround I can think of is to supply a useless parameter value e.g. CREATE PROCEDURE GetOrderProcessIntervals ( this_not_used INTEGER = 0 ) AS SELECT OrderID, OrderDate, ShippedDate, DATEDIFF('D', OrderDate, ShippedDate) AS order_process_interval_days FROM Orders; Now SELECT * FROM GetOrderProcessIntervals; fails with a 'missing parameter' error. So the choice seems to be to ask users to ignore daft parameter or be a little less strict on normalization rules. Jamie. -- |
#5
|
|||
|
|||
How do you mulitply in a field?
I don't see why this should be contradictory. The fact is the calculated
column is not "stored" in a Query, so the cases are not even similar. By the same reasoning, we should never store a Join of two tables because that would produce data redundancy. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Jamie Collins" wrote in message oups.com... On 15 Aug, 22:05, fredg wrote: If you saved your query with the calculated column as a stored Query (Title Case) object then this would become a virtual table. You would seem to be saying that denormalization in a base table is wrong but fine in a virtual table. That sounds contradictory. |
#6
|
|||
|
|||
How do you mulitply in a field?
On 16 Aug, 13:04, "Jeff Boyce" -
DISCARD_HYPHEN_TO_END wrote: I'm curious why the same admonition about rarely needing to store calculated values in a table would have to be applied to views, i.e., your "virtual tables"? Do you have a reference so I can read up more on this? I would have no idea where to look for such a reference. Normalization applies to logical tables. The distinction between base tables and virtual tables is a SQL (the language) concept. You can create a SQL table with no key; likewise, you can create a SQL table that is denormalized. But if you buy in to the idea of aiming for the highest possible normal form -- and I suggest that you should -- then why would you not apply it virtual tables? Should a VIEW have a key? I'd say so. I'm probably not the best person to defend a normalization stance because personally I don't have too much of a problem with calculated columns in a base table a) if there's a good reason for doing so and b) when integrity constraints are present to prevent values going 'out of sync'. A phrase that comes to mind from long past studies for taxation exams: "Strict rules modified in practice." Jamie. -- |
#7
|
|||
|
|||
How do you mulitply in a field?
On Aug 16, 2:16 pm, "Roger Carlson"
wrote: I don't see why this should be contradictory. The fact is the calculated column is not "stored" in a Query, so the cases are not even similar. Since when did normalization have anything to do with physical storage?! Consider that in some SQLs (e.g. Oracle) VIEWs can be materialized i.e. their data *are* physically stored. I repeat: normalization applies to logical tables. By the same reasoning, we should never store a Join of two tables because that would produce data redundancy. A JOIN in a query does not by necessarily result in a denormalized structu you have to consider the whole: SELECT clause, WHERE clause, GROUP BY clause, etc. But yes, the reasoning is the same: reundant data in a VIEW, regardless of how it was derived, consistutes denormalization. Jamie. -- |
#8
|
|||
|
|||
How do you mulitply in a field?
1) Normalization has always been about physical storage. The specifics
about how it's stored is unimportant, but the fact that the data *is* stored is. At its heart, normalization is about reducing redundant data, that is, the data that is physically stored. -- --Roger Carlson MS Access MVP Access Database Samples: www.rogersaccesslibrary.com Want answers to your Access questions in your Email? Free subscription: http://peach.ease.lsoft.com/scripts/...UBED1=ACCESS-L "Jamie Collins" wrote in message oups.com... On Aug 16, 2:16 pm, "Roger Carlson" wrote: I don't see why this should be contradictory. The fact is the calculated column is not "stored" in a Query, so the cases are not even similar. Since when did normalization have anything to do with physical storage?! Consider that in some SQLs (e.g. Oracle) VIEWs can be materialized i.e. their data *are* physically stored. I repeat: normalization applies to logical tables. By the same reasoning, we should never store a Join of two tables because that would produce data redundancy. A JOIN in a query does not by necessarily result in a denormalized structu you have to consider the whole: SELECT clause, WHERE clause, GROUP BY clause, etc. But yes, the reasoning is the same: reundant data in a VIEW, regardless of how it was derived, consistutes denormalization. Jamie. -- |
#9
|
|||
|
|||
How do you mulitply in a field?
If you don't have a problem with storing a calculation in a base table, then
what is the normalization issue when it comes to queries? I doubt you are arguing against normalization in base tables, but where are you trying to go with this? How would you go about performing calculations? If the results are stored in a table, something can be done to force recalculation as needed, but what does that gain you? "Jamie Collins" wrote in message oups.com... On Aug 16, 2:16 pm, "Roger Carlson" wrote: I don't see why this should be contradictory. The fact is the calculated column is not "stored" in a Query, so the cases are not even similar. Since when did normalization have anything to do with physical storage?! Consider that in some SQLs (e.g. Oracle) VIEWs can be materialized i.e. their data *are* physically stored. I repeat: normalization applies to logical tables. By the same reasoning, we should never store a Join of two tables because that would produce data redundancy. A JOIN in a query does not by necessarily result in a denormalized structu you have to consider the whole: SELECT clause, WHERE clause, GROUP BY clause, etc. But yes, the reasoning is the same: reundant data in a VIEW, regardless of how it was derived, consistutes denormalization. Jamie. -- |
#10
|
|||
|
|||
How do you mulitply in a field?
On Aug 16, 3:17 pm, "Roger Carlson"
wrote: Normalization has always been about physical storage. The specifics about how it's stored is unimportant, but the fact that the data *is* stored is. At its heart, normalization is about reducing redundant data, that is, the data that is physically stored. Are you saying that normalization does not apply to a VIEW unless it's materialized? I've never heard this distinction made before and it doesn't sound correct to me. Let's go slow. Normalization applies to tables. A SQL base table is a table and a SQL VIEW (materialized or otherwise) is a table. Where do you think I'm going wrong? Jamie. -- |
Thread Tools | |
Display Modes | |
|
|