Hi Jacky,
For your first issue, check out Access MVP Allen Browne's paper:
Inventory Control: Quantity on Hand
http://allenbrowne.com/AppInventory.html
2. Calculated results be stored in the same table for record purpose.
In general, you do not want to store the results of a calculation. Doing so
violates database normalization rules. Here are some quotes that I like to
share on this subject:
http://www.seattleaccess.org/
(See the last download titled "Understanding Normalization" in the
Meeting Downloads section)
<Begin Quote (from page 23 of document)>
"The most important point for you to remember is that you will always
re-introduce data integrity problems when you de-Normalize your structures!
This means that it becomes incumbent upon you or the user to deal with this
issue. Either way, it imposes an unnecessary burden upon the both of you.
De-Normalization is one issue that you'll have to weigh and decide for
yourself whether the perceived benefits are worth the extra effort it will
take to maintain the database properly."
<End Quote>
As fellow Access MVP John Vinson likes to say "Storing calculated data
generally accomplishes only three things: it wastes disk space, it wastes
time (a disk fetch is much slower than almost any reasonable calculation),
and it risks data validity, since once it's stored in a table either the
Total or one of the fields that goes into the total may be changed, making
the value WRONG."
Also see:
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101
If you really decide that you absolutely must store the results of a
calculation, then by all means study up on how to implement a JET constraints
implemented at the table level, to prevent the very problem that Michael and
John warn against.
Tom Wickerath
Microsoft Access MVP
http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
