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
  #11  
Old August 16th, 2007, 04:08 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:19 pm, "BruceM" wrote:
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?


It's true, I'm not very strict about normalization in base tables,
ditto VIEWs. Quite strict, though, because I require a good reason and
assurance of data integrity. But equally strict because both are
tables. Usually I have no good reason, consequently I make it a stored
proc.

I think those who are very strict about normalization in base tables
should be very strict about normalization in virtual tables.

But you know me: I think every table (including VIEWs) should have an
enterprise key defined by business rules; if the only unique
constraint is defined on a sole autonumber column then I wouldn't
consider the object to be a table!

Jamie.

--



  #12  
Old August 16th, 2007, 05:58 PM posted to microsoft.public.access.tablesdbdesign
BruceM[_2_]
external usenet poster
 
Posts: 1,763
Default How do you mulitply in a field?

OK, that clarifies what you were saying.
Regarding autonumber or other arbitrary PKs: If I have a listing of
vendors, what is the *unchanging* unique constraint? It is not name or
address in any combination, which are subject to change (thereby meaning
related records would also need to change). Maybe it could be the tax ID
number, but then you would need that information first, which is not always
practical. Or maybe it would be an arbitrary number in combination with
other fields that are not subject to change, such as...hmm.
Or maybe cascading updates of multi-field keys are not a problem. I would
rather avoid them, especially if another database links to that table.
You will not change my mind on the subject, nor I yours. But understand
that I *never* argued in favor or creating a table for which the "only
unique constraint is defined on a sole autonumber column". A record's
uniqueness is one thing. The means of identifying it in relationships is
another. The two may be the same, or they may not. I'm not going to bind
myself to an absolute rule based on somebody's design theory. Rather, I
will make the necessary choices as I go.

"Jamie Collins" wrote in message
ups.com...
On Aug 16, 3:19 pm, "BruceM" wrote:
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?


It's true, I'm not very strict about normalization in base tables,
ditto VIEWs. Quite strict, though, because I require a good reason and
assurance of data integrity. But equally strict because both are
tables. Usually I have no good reason, consequently I make it a stored
proc.

I think those who are very strict about normalization in base tables
should be very strict about normalization in virtual tables.

But you know me: I think every table (including VIEWs) should have an
enterprise key defined by business rules; if the only unique
constraint is defined on a sole autonumber column then I wouldn't
consider the object to be a table!

Jamie.

--





  #13  
Old August 16th, 2007, 07:53 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default How do you mulitply in a field?

Again, this is your assertion, but I'm looking for the basis.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jamie Collins" wrote in message
ps.com...
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.

--




  #14  
Old August 17th, 2007, 02:20 AM posted to microsoft.public.access.tablesdbdesign
Beetle
external usenet poster
 
Posts: 1,254
Default How do you mulitply in a field?

Although your post produced an interesting discussion, I'm guessing you were
looking for an answer. I'm not an Access MVP or anything, just a regular guy,
but I would agree with fredg that you should not try to store calculations in
a table. Tables are for storing raw data. You use queries to manipulate the
data and perform calculations. I doubt the survival of the free world depends
upon your database, so if your queries aren't technically "normalized", I
think you'll be OK.

"wazabbbi" wrote:

I'm a newbee at this whole access thing. The purpose of this database is to
calculate fees and total fees collected for a project. I wanted to be able to
store the result in the same table. I was able to have the result calculated
on the form but the version I have of access is so old that I can only run
reports from a table. To further complicate things I have another field that
I want to have the total of the inspection fee and two other fields displayed
and stored. In excel its simple enough to do but I'm having trouble figuring
out how to do it in access. Eventually I'd like to run reports showing total
fees collected for a set period of time but also for a particular applicant
(our applicants may have multiple projects).

Can you guys please elaborate as to the best way for me to accomplish my
goal.

Thanks =)

"Jamie Collins" wrote:

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.

--



  #15  
Old August 17th, 2007, 02:21 AM posted to microsoft.public.access.tablesdbdesign
wazabbbi
external usenet poster
 
Posts: 2
Default How do you mulitply in a field?

I'm a newbee at this whole access thing. The purpose of this database is to
calculate fees and total fees collected for a project. I wanted to be able to
store the result in the same table. I was able to have the result calculated
on the form but the version I have of access is so old that I can only run
reports from a table. To further complicate things I have another field that
I want to have the total of the inspection fee and two other fields displayed
and stored. In excel its simple enough to do but I'm having trouble figuring
out how to do it in access. Eventually I'd like to run reports showing total
fees collected for a set period of time but also for a particular applicant
(our applicants may have multiple projects).

Can you guys please elaborate as to the best way for me to accomplish my
goal.

Thanks =)

"Jamie Collins" wrote:

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.

--



  #16  
Old August 17th, 2007, 05:26 AM posted to microsoft.public.access.tablesdbdesign
[email protected]
external usenet poster
 
Posts: 2
Default How do you mulitply in a field?

Normalization is the level of efficiency your data model stores data
that is INPUT. A View is an OUTPUT of the normalized data and has no
buisness being part of the data storage model ... even if the data
that is OUTPUT to the View is stored in your db (ie a Make Table query
in Access), that block of data is NOT part of the data storage model
for your application. If your datamodel is dependant upon on OUTPUT
(ie a View or Make-Table query), then I would stick my neck out and
claim that you are not normal.

Regards,
Brent Spaulding | datAdrenaline | Access MVP

  #17  
Old August 17th, 2007, 05:52 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default How do you mulitply in a field?

On Thu, 16 Aug 2007 18:21:29 -0700, wazabbbi
wrote:

the version I have of access is so old that I can only run
reports from a table.


Ummm....

Access 2.0, back 15 years ago, let you base reports on queries. And I'm pretty
certain that 1.0 and 1.1 did also.

John W. Vinson [MVP]
  #18  
Old August 17th, 2007, 09:10 AM 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, 19:53, "Jeff Boyce" wrote:
I'm looking for the basis.


What, that a VIEW is a logical table? Have you tried doing a google
search? e.g.

sql view virtual OR logical table
http://www.google.co.uk/search?q=sql...+logical+table

Now try the same using e.g.

normalization view virtual OR logical table
http://www.google.co.uk/search?q=nor...ogical+tab le

though you could drop the word 'virtual' from the search because
'virtual tables' are a SQL (the language) concept.

From these readings you may glean the following formal process:


1) As Mrs Beeton would say, "First, catch you your business model."
2) Design a logical model applying the rules of normalization to
create logical tables and the relationships between them.
3) Implement your logical model in SQL (or other relational
technology); the logical tables from your logical model sometimes are
directly implemented as a physical SQL base table and sometimes not.

So, in my book, my SQL implementation must be true to my logical model
and in turn my logical model must be true to normalization. If you are
looking for a reference that applies normalization to VIEWs then I
don't think you will find one because there is a stage in between --
logical model -- that cannot be disregarded. I don't see anything in
this process that offers 'normalization immunity' to virtual tables.

Jamie.

--

  #19  
Old August 17th, 2007, 10:24 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default How do you mulitply in a field?

On 17 Aug, 05:26, wrote:
Normalization is the level of efficiency your data model stores data
that is INPUT. A View is an OUTPUT of the normalized data and has no
buisness being part of the data storage model ... even if the data
that is OUTPUT to the View is stored in your db


Normalization only applies to INPUT? Base tables = INPUT, virtual
tables = OUTPUT?

INSERT INTO MyView...
UPDATE MyView SET ...
DELETE FROM MyView WHERE...

Where did you get such notions?!

If your datamodel is dependant upon on OUTPUT


My physical implementation in SQL is dependent on my logical model,
not the other way around. My logical model does not directly consider
INPUT and OUTPUT, those are for the font end.

My SQL implementation sometimes relies on VIEWs. Example: one
application requires a time-valid state (history table with periods)
and a second application is only interested in the current state,
therefore I create a VIEW (...WHERE end_date IS NULL, 'hide' the
base table while 'exposing' the VIEW via privileges, use INSTEAD OF
triggers and WITH CHECK OPTION to manage updates to the VIEW. Though
the users of the second application may discover it's a VIEW rather
than a base table they should not care because logically a table is a
table is a table.

(ie a Make Table query
in Access), that block of data is NOT part of the data storage model
for your application.


I don't get your 'Make Table query' example. IMO no application should
be creating permanent tables on the fly. Because though Access/Jet SQL
does not support the CREATE TEMPORARY TABLE or similar syntax (unless
the Help is to believed and I suggest it isn't:
http://office.microsoft.com/en-gb/ac...314411033.aspx) I can
see the temptation, however I'm told that dropping an re-creating
tables causes file bloat.

I have tables in my SQL implementation that are not part of my logical
model. Example: permanent load tables I can populate, operate on using
SQL to populate other tables then clear down; the most frequent use it
to normalize 'non-relational' data (e.g. csv) therefore such tables
are denormalized out of necessity. I say that tables that are part of
the logical mode should be normalized (unless there's a good reason to
denormalize, always ensuring data integrity).

I have permanent auxiliary tables that are denormalized. Example:
Calendar table with columns for date (instant), year, month, day,
week, is_weekday, etc. Theses tables are to help me and the users
write simply, easy to maintain queries. There's always costs when
denormalizing but here the benefits outweigh them.

I have permanent base tables that are denormalized to work around a
physical limitations in the SQL DBMS. Example: I must handle a real
life enterprise key, for which the trusted source is a government
agency, comprising one thousand ASCII characters (the civil servant
who designed it has since offered me a personal apology g), the SQL
DBMS's I use employs indexes to implement unique constraints where
there's a limit on the number of bytes an index value can comprise, so
I have to store a hash of the real key value so that my integrity
constraint (CHECK, trigger, etc) used to ensure uniqueness can use the
non-unique index on the hash for performance reasons. The hash does
not exist in the logical model so I figure I haven't denormalized
that. In the physical implementation I need the performance *and* I
need the uniqueness and all is fine by me because the hash is
constrained to be a true hash of the actual value i.e. can't do out of
sync. You can consider my table to denormalized but my conscience is
clear g.

I would stick my neck out and
claim that you are not normal.


In my country (UK), it is the custom to ROFL when a devastatingly good
pun is made.

Jamie.

--


  #20  
Old August 17th, 2007, 11:12 AM posted to microsoft.public.access.tablesdbdesign
Graham R Seach[_2_]
external usenet poster
 
Posts: 51
Default How do you mulitply in a field?

Hi Jamie,

Technically, normalisation applies only to the logical relation, not to a
physical table or any view of it. As I would hope you know, any
visualisation of the data conained within a table, is a view; and it's
implemented as a view behind the scenes. Views are not virtual tables, they
are visualisations of datasets. If you open a "table" to look at its data,
you're visualising that data through an additional layer called a view, not
the table itself, and certainly not the relation. You can never "see" a
table's data directly, because a table is just the physical implementation
of a structured storage mechanism. Whether I choose to view a slice of my
data horizontally, vertically, or include joins or calculated fields, the
rules of normalisation applied to the relation(s) remain(s) unchanged,
because I have not changed the relation(s). But to create a calculated field
in a relation would indeed constitute a breach of normalisation rules.

Strictly speaking, materialised views can be normalised or un-normalised,
and that's OK, because they are not the primary data storage relations. They
are simply views that have been materialised for purposes other than data
storage. They're equivalent to temp tables, so the rules don't apply.

Just as an aside; in a later post you state that we should always aim for
the highest normal form. That's not entirely accurate or desirable. I'm sure
that when you think about it, you'll agree. DKNF or (heaven forbit) Lossless
Joins are not for the feint of heart; nor are they anywhere near practical
in 99.99% of cases. Even in data warehouses, we rarely go beyond 4NF.

You're correct when you quote, "Strict rules modified in practice". A
classic example is financial data models, where storing current balances in
tables are the norm. The relations are designed that way, thus lowering
normal form.

In any case, you have to make the distinction between (a) the storage of
data, and (b) the use of that data. Pure storage requires normalisation. Use
of the data requires whatever form is necessary to convert that data into
information or knowlege. Therefore, denormalising views by including
calculated fields is OK, because it is an output function, not a data
storage function. Data modelling 101.

I think those who are very strict about normalization in base tables
should be very strict about normalization in virtual tables.
No. One is not the other.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


"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.

--




 




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 07:44 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.