Hi Dan,
These calcs work fine on the form, however, they do not move
to the table with the rest of the info. Any suggestions????
Yes. It's seldom a good idea to store the results of a calculation in a table. Doing so violates
both 2nd and 3rd normal forms of database design. It's usually much better to run the calculation
on-the-fly, as the values are needed. Consider the following quote from database design guru
Michael Hernandez:
"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."
If it was later discovered that an incorrect surgery date or DOB was entered into the database,
you could introduce data integrity problems if one or both of these values was changed, but the
calculated value was not updated.
How do I get the case count field in the table to start at 1?
An autonumber field is an excellent choice for a primary key in most instances. However, it
should be considered meaningless. It is not designed to provide a count of records. My suggestion
is to simply hide the autonumber PK on all forms and reports. If you need a record count on the
form, and you don't want to display the navigation buttons which includes a count of records,
then you can use a textbox with the DCount domain aggregrate function.
The only way to get an autonumber to restart at 1 is to delete all records from the table, and
then do a compact and repair (Tools > Database Utilities > Compact and repair...).
Any other suggestions would be greatly appreciated as I am fairly
new(OK...really new) to access.
Here are some good articles to become familiar with:
Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp
Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266
Database Design
You should spend some time gaining an understanding of database design and normalization before
attempting to build something in Access (or any RDBMS software for that matter). Here are some
links to get you started. Don't underestimate the importance of gaining a good understanding of
database design. Brew a good pot of coffee and enjoy reading!
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://www.eade.com/AccessSIG/downloads.htm
(See the last download titled "Understanding Normalization")
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
Also recommended: Find the copy of Northwind.mdb that is probably already installed on your hard
drive. Study the relationships between the various tables (Tools > Relationships...)
Name AutoCorrect
Name AutoCorrect was introduced with Access 2000. This is a most buggy feature. I recommend
always deselecting the options to track & perform corrections via Tools > Options ---> General
Tab. Unfortunately, these options are selected by default in all new databases, so you should
remember to always deselect it. For more information, see the following article:
Failures caused by Name Auto-Correct
http://members.iinet.net.au/~allenbrowne/bug-03.html
References
Solving Problems with Library References (Allen Browne)
http://members.iinet.net.au/~allenbrowne/ser-38.html
Access Reference Problems (Doug Steele)
http://members.rogers.com/douglas.j.steele/AccessReferenceErrors.html
Gem Tips
http://www.access.qbuilt.com/html/gem_tips.html
Any suggestions on where one might get some professional assistance(paid)
to assist with this project would be nice as well.
Ah...you're likely to get LOTS of bites with this kind of fresh bait offered. I'd like to suggest
QBuilt.com (
http://www.qbuilt.com/6901.html).
Tom
_______________________________
I am building a medical database that establishes a log of patients that have
undergone anesthesia in my practice. In my form I have two calculated
fields..one is for the age of the patient (taken from the date of surgery and
DOB) and the time in minutes of the anesthesia (taken from the start and stop
times). These calcs work fine on the form, however, they do not move to the
table with the rest of the info. Any suggestions????
Second, While setting the form up, I had some trial and errors. When I was
ready to enter information, the case count field (autonumbered)(to keep count
of cases per month) starts at 16 although the record number on the bottom of
the form started at 1. How do I get the case count field in the table to
start at 1?
Any other suggestions would be greatly appreciated as I am fairly
new(OK...really new) to access. Thanks for your assistance in advance.
Any suggestions on where one might get some professional assistance(paid) to
assist with this project would be nice as well.
GoToLeep (Dan)
