---------- "Nashville DAS said:
I am interested in understanding better why a database can't dynamically
update a record value (issue 1) and also why a calculated record value can't
be stored in a table (issue 2).
John,
Both _can_ be done, they are not impossible. But it is good practice
not to store calculated values (with some exceptions). I'll try to
explain why. From this POV there's a fundemantal difference between a
spreadsheet, which automatically updates all cells containing
formulas, and an Access database table, where you cannot store
formulas at all, only values. Which means, each time a term of an
expression is changed, the developer has to take care of all dependent
expressions. And as there can be various situations, network problems,
breakdowns, delays between updating the form and data written to hard
disk this task will be never 100% waterproof, even if one uses
transactions.
That's why the few exceptions are mostly calculated values from
numbers that once entered won't change anymore.
On a more theoretical level, it's about the so-called normalization
rules, appliable to all relational databases. I'll cite here a
Webopedia article, the second normal form relates to the calculated
values:
"Normalization:
In relational database design, the process of organizing data to
minimize redundancy. Normalization usually involves dividing a
database into two or more tables and defining relationships between
the tables. The objective is to isolate data so that additions,
deletions, and modifications of a field can be made in just one table
and then propagated through the rest of the database via the defined
relationships.
There are three main normal forms, each with increasing levels of
normalization:
* First Normal Form (1NF): Each field in a table contains different
information. For example, in an employee list, each table would
contain only one birthdate field.
* Second Normal Form (2NF): No field values can be derived from
another field. For example, if a table already included a birthdate
field, it could not also include a birth year field, since this
information would be redundant.
* Third Normal Form (3NF): No duplicate information is permitted. So,
for example, if two tables both require a birthdate field, the
birthdate information would be separated into a separate table, and
the two other tables would then access the birthdate information via
an index field in the birthdate table. Any change to a birthdate would
automatically be reflect in all tables that link to the birthdate
table.
There are additional normalization levels, such as Boyce Codd Normal
Form (BCNF), fourth normal form (4NF) and fifth normal form (5NF) .
While normalization makes databases more efficient to maintain, they
can also make them more complex because data is separated into so many
different tables."
I know this sounds strange and you'll probably ask why should one
comply, but the practice has shown, on the long run development is
easier if you really stick to the rules. Disregarding them might look
practical at the beginning, but sooner or later you'll end up in a
dead-end where no workaround helps.
I'd be happy to spread these functions around between multiple records if
necessary but it sounds like even that is not possible.
Hm, I not quite understand what do you mean. I guess it would be best
if you'd tell me more about the whole thing and describe, what would
you want to achieve. For ex the question of how would you identify the
records is important because there are no row numbers like in Excel -
could be you'd need changes in the data structure.
I'll give you an example.
If the 10 values to be averaged are for ex repeated measurements of
the same property in a physical experiment, and they all belong to the
same object - say, one weighs a given object 10 times - then the
object +experiment conditions would be stored in an Experiments table,
and the individual measurement data in an other table. The two tables
would be in a 1:n relationship, one record in Experiments(= 1 object)
would 'own' up to 10 records in the Measurements table. The object ID,
also stored in the Measurements table would define the relationship
and thus these records, so they can be selected easily for
calculations etc.. And in this case I'd actually think about storing
the average value too - but definitely not in the Measurements table.
It would belong in the Experiments table.
Surely there must be accounting databases that do this. Is this a unique MDB
issue? Seems like DB3 didn't have this problem...but that's old memory
recall and may not be accurate..
Don't know DB3, but I guess it would have had the same problem as
Access. The 'big' DBMS like Oracle or SQL Server do have a feature
called stored procedures, where you can perform whatever operations in
the table itself, but using such a backend only for being able to
store calculated values is like, hmm, buying a Tuareg just to get your
toast bread from around the corner
And the normalization rules are
still valid...
So, if I understand correctly, an equation as below can not function....and,
the calculated value can not be stored...?
=(([Grade1]+[Grade2]+[Grade3]....+[Grade10])/10)
I don't know how your user interface looks like, but if this is on a
form, you could create a text box in the form's footer and put this
expression in the control source property. However, the field names
Grade1, Grade2 etc. look pretty suspect to me - "don't store
information in field names" being another strongly recommended rule
(What if some time there will be more or less than 10 grades...?)
And this also contradicts IIUC what you wrote about 10 _records_.
Grade1 to Grade10 rather look like 10 fields in the same record.
Thank you for helping me with this. This has been a pet project of mine for
almost four years. It would make me very happy to find a work-around or
better yet a solution...
We'll try to sort it out
I allow myself to recommend you two books to help you understand how a
relational DB is working:
"Database Design for Mere Mortals" by Michael Hernandez
"Access for Dummies" (don't know the author, but there's a whole
series of "...Dummies" books - in spite of the title, they are good,
specially for beginners).
And of course this thread, you're most welcome...
Best regards
Emilia
Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de