A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How do you mulitply in a field?



 
 
Thread Tools Display Modes
  #1  
Old August 15th, 2007, 09:34 PM posted to microsoft.public.access.tablesdbdesign
wazabbbi
external usenet poster
 
Posts: 2
Default 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  
Old August 15th, 2007, 10:05 PM posted to microsoft.public.access.tablesdbdesign
fredg
external usenet poster
 
Posts: 4,386
Default 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  
Old August 16th, 2007, 12:10 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old August 16th, 2007, 01:04 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 1,555
Default 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  
Old August 16th, 2007, 02:16 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 824
Default 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  
Old August 16th, 2007, 02:28 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old August 16th, 2007, 02:45 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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  
Old August 16th, 2007, 03:17 PM posted to microsoft.public.access.tablesdbdesign
Roger Carlson
external usenet poster
 
Posts: 824
Default 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  
Old August 16th, 2007, 03:19 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default 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  
Old August 16th, 2007, 03:40 PM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 01:23 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.