Hi Greg,
I forgot to mention--although it is included in the various database design
papers that I provided links to--that other advantages of proper database
design include:
1.) Easier data entry. You can provide combo boxes on forms, so that people
can pick from lists, instead of having to type in the value each time. Later
on, if you need to change the spelling of an item in the list, you will only
need to edit a single record in the lookup table.
Notes:
I recommend using autonumber primary keys in all tables, including your
lookup tables. Store the key value as a long integer foreign key--not the
text itself--in the table that is child to the lookup table.
I do not recommend using lookup fields at the table level.
2.) Less chance of misspelled data, which can result in inaccurate query
results. You have less chance of misspelled data when a person can pick a
value from a list, instead of having to type it in each time. In Access,
click on Tools > Analyze >Table. Read the first screen of the table analyzer
wizard: "The Table Analyzer / Looking at the problem."
Search your hard drive for a sample database called "Northwind.mdb". Open
the Orders form. The ability to pick a customer from a combo box is easily
provided by having customer information in a separate table. Note that this
sample provides plenty of examples of table lookups, which are seen by many
as a creation of the evil one. See the second commandment shown here:
http://www.mvps.org/access/tencommandments.htm
Tom
_______________________________
:
Thanks to all three of you for taking the time to read my question. It seems
as if I need to study up on data base design.
_______________________________
:
Hi Greg,
Here are some links to get you started on database design:
http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf
http://support.microsoft.com/?id=234208
http://support.microsoft.com/?id=289533
http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")
The last link shown above is to a paper written by database design guru
Michael Hernandez. Your question is answered in the first pages of this
document. See the section titled "Modification Anomalies", which starts on
the bottom of page 2.
A table should represent a single subject! Your massive table likely has
many-to-many relationships built within it. If you need to track a new
attribute that is similar to other attributes that you are already tracking,
your one-table design will require that you add yet another field. That means
that you'll have the pleasure of having to modify any queries, forms, and
reports in order to display your new attribute. A good rule of thumb to
remember is the following:
"Records are cheap. Fields are expensive".
In other words, if your database design requires that you add field(s) to
track similar data to what you are already tracking (monthly sales, for
example, with fields named: Jan2005, Feb2005, Mar2005, Apr2005, May2005,
etc.) this should be a big red flag. Your design should allow similar data to
be entered without requiring design changes to any objects in the database.
Don't underestimate the importance of gaining a good understanding of
database design. Brew a good pot of tea or coffee and enjoy reading!
Tom
http://www.access.qbuilt.com/html/expert_contributors.html
_______________________________
:
I am new to Access, and have made one large table with around 100 or so
fields. In reading these posts I am getting the impression that I should
have more than one table. If all of my forms and queries work fine, why do I
need more tables?
