Calculations in tables?

[ACC]

I built forms that have Expressions or calculations in
them but when the calculations are made the results are
not stored in my tables. Is there any way to get the
calculations that are made in my forms, stored in my
tables. The User's guide says that in most cases you don't
want the results of calculations in tables, but i want
them to be there.
can anybody help????

 

[Sal]

It is against the principals of database theory to keep
redundant data within a table.
Do not keep this calculated values inside of a table, all
that does is create a larger database file for no reason.
Just go ahead and calculate them as you need them.

Sal

 

[Jen]

Calculations are usually done via a query or like you
said, within forms. To put a calculation in a table is
considered a no-no in RDBMS design. One of the reasons
for this is that the raw data that the calculations use
(which are presumably in your table) can at any moment
change. If you store a calculated value within that
table, the calculation wouldn't change and the
information would not be valuable, as a matter of fact,
it'd be wrong. The other reason is because this is what
functions and queries are for.

What it sounds like you're maybe attempting to do is to
write a query based on the table in question. Perform
all of your calculations within the query (via an access
expression - ie; Addition: Field1 + Field2) and then
base the form's recordsource property on the query.

Hope that helps.

Regards,
Jen

 

[Jeff Boyce]

In addition to the cautions against storing calculated values offered by Sal
and Jen, let me offer a valid exception to this very reasonable general
rule...

If I am keeping a product list, with "current" prices, and creating Orders,
and OrderDetail rows, I DON'T want to point back to the Products table and
look up prices, because the price on the order was the one valid at that
point in time (and not necessarily true now).

Of course, the way to keep this situational more relational would be to put
begin- & end-dates on product X price rows, so that you are only looking up
the current price, but could, with the rowID from an earlier product X price
combination, also look up that "at time of order" price.

Good luck!

Jeff Boyce
<Access MVP>

 

[Mike Sherrill]

It is against the principals of database theory to keep
redundant data within a table.

Can you back that up? Chapter and verse, please.

 

[Mike Sherrill]

If I am keeping a product list, with "current" prices, and creating Orders,
and OrderDetail rows, I DON'T want to point back to the Products table and
look up prices, because the price on the order was the one valid at that
point in time (and not necessarily true now).

In that case, "current" price and price on the date of the order are
different attributes. No redundancy there.

 

[Duane Hookom]

How about http://www.frick-cpa.com/ss7/Theory_RelationalDB.asp

 

[mdr02125]

though some noted it is against good database design to store
calculated values, there are a number of reasons that sometimes it can
be good to do:



1. you need to store some sort of historical value (see example above
about what the current price was on the date the order was placed,
perhaps not today's price) and you do not have the resources (staff
time or funds to hire more, knowledge, etc.) to set up a speical
table with starting and ending dates for each price change for each
item (this is better design but requires more coding which you may
not have the staff resources or staff knowledge on hand to do in time
to meet your deadline).



2. roll-up tables in some circumstances are useful

Example - In our call center we calculate calls per hour for > 100
agents each day. this is used for quarterly and annual reviews. Due to
rounding problems (and additional math steps too complex to mention
here) the data is stored in a rollup table and monthly/quarterly/annual
averages are computed from it. Otherwise you keep re-writing the formula
for each new report, and if one time you go to one decimal point and the
next to 2 (such mistakes can happen) ... this can adversely affect the
amount of someone's bonus.

Note: a roll-up table is created by using an Append Query(ies) each day
to do the calculations you choose (or weekly or whatever time
period you set up)



real world vs. theory

There are better ways in terms of database theory and design to do this
but Access crashes if the calculations are all done when the monthly
much less quarterly reports are printed. For now budget has no been
allocated to move to SQL Server or to hire additional staff to tweak
Access further, and having had enough late nights at work myself this
works in the real world -- The point is sometimes shortcuts can be best
even if it is not "best" from a purely database design standpoint.



Tip: try to be aware of what shortcuts you are taking and why, and try
to involve management in deciding to take such shortcuts to meet
deadlines or to push back a deadline to do it "right" -- this will
make it easier down the road to get management approval for the staff
time when you (may) have to convert things to do it the (theoretically)
"right" way.

 

[Tim Ferguson]

1. you need to store some sort of historical value (see example above
about what the current price was on the date the order was placed,
perhaps not today's price)

In which case it's not calculatable...

Due to
rounding problems (and additional math steps too complex to mention
here) the data is stored in a rollup table and monthly/quarterly/annual
averages are computed from it.

Ditto, if you mean that the final values cannot be reliably reconstructed
from the original data every time.

The problem for me is that there is nothing in R theory that talks about
"calculated" or "repeated" or "redundant". The theory relies on definitions
like "functional dependency", and when the questions above are cast in this
light, then they become very easy to answer. If a particular attribute is
dependent only on the whole of the primary key, then it should be included.
Otherwise it shouldn't. Therefore, Rect.SurfaceArea is not dependent on
RectNumber, but on Rect.Height and Rect.Width; Order.TotalAmount is
dependent on a set of records in OrderDetails; but OrderDetails.AgreedPrice
is dependent only on (OrderDetails.OrderNumber, OrderDetails.OrderItem)
because it cannot be recovered from the Prices table if that value is
likely to change. Similarly, if the end result of your roll up calculations
is no longer functionally dependent on the base data, then it needs to be
stored appropriately.

real world vs. theory

As seen above, once the theory is properly understood, there is no
conflict. The logical solution is always going to be the right one, and you
will find that R theory is way ahead of you if you think you have found an
exception.

There are better ways in terms of database theory and design to do this
but Access crashes if the calculations are all done when the monthly
much less quarterly reports are printed.

The difficulty is, indeed, in the move to the physical solution, and the
errors that inadequate hardware forces on you. But at least the compromises
are made explicit.

Tip: try to be aware of what shortcuts you are taking and why,

hundred per cent agree.


B Wishes


Tim F

 

[Mike Sherrill]

How about http://www.frick-cpa.com/ss7/Theory_RelationalDB.asp

Can you be a little more specific than just pointing to an accounting
firm's interpretation of Codd's original 12 rules?

 

[Duane Hookom]

I was referencing
"Each piece of data must be logically accessible by referencing 1) a table;
2) a primary or unique key value; and 3) a column " which to me suggests
single location not plural.

 

[Mike Sherrill]

I was referencing
"Each piece of data must be logically accessible by referencing 1) a table;
2) a primary or unique key value; and 3) a column " which to me suggests
single location not plural.

Read it again. It's talking about how you access values--by supplying
the name of a table, the name of a column, and the value of a key--not
about redundancy.

All data, including redundant and derived data, is accessible the same
way--by supplying the name of a table, the name of a column, and the
value of a key.