Pin Me

MS Access Databases: Tables are the Foundation

written by: Curt Smothers•edited by: Michele McDonough•updated: 12/26/2009

Tables are the foundation of an MS Access database. Just as a weak building foundation will mean big problems later, a poorly designed database table will ruin the best designed forms, queries and reports. Tables form the basics on database planning and design.

  • slide 1 of 7

    Database Planning and design

    MS Access is a powerful database application that, unlike the Excel spreadsheet, the new user needs to master a learning curve and think globally. The global thinking involves considering your “end product" even before you enter your data. For this reason, database professionals and consultants devote as much time planning database “output" (what reports or information the database will produce) as they do with “input" (how the data base will accept and process the data).

  • slide 2 of 7

    "Garbage In, Garbage Out"

    Experienced database designers know that good planning and data design must happen before the data is entered into the database. A grouped report from a large database that is based on a grouping of zip codes, for example, cannot be conveniently generated unless the database was designed with a separate field for the zip code. Likewise, a database design that does not include some level of “quality control" at the data entry level will eventually result in a cluttered database of questionable value.

  • slide 3 of 7

    Fields, Records and the Table

    The quality control begins with the thoughtful design of your MS Access database table. A table, in turn, is only as good as the fields we design that make up the individual records, which are our separate groupings of vital information on a person, product, or other item tracked, grouped and reported by our data base. Think of a database table as a sort of warehouse for your information. We keep our store of information orderly by designing our “containers" (fields and records) in a consistent way that prevents outside “contamination" (i.e., by preventing bad data from being entered).

  • slide 4 of 7

    Properties Are Everything

    In database table design, we consider both the types of fields we need and the properties (categories) and entry rules we need to keep entry errors to a minimum. In a customer database, for example, we would maintain only personal information (name, address, phone, etc.). We would also attach data entry “rules" to each new field. (For example, is it permissible to leave a field empty? What happens when a zip code is entered with fewer than the required five initial digits?) Would accessing a “look-up" table be advisable as an aid to more accurate data entry? These considerations are especially important if persons unfamiliar with database design are entering data into the database.

  • slide 5 of 7

    Table Design = "Do it right the first time!"

    So there are a ton of considerations that go into the design of your database table (its records and fields). The temptation in using any computer application is to jump right in and make it work. The quick-start approach works fine with word processors and spreadsheets, but can lead to serious problems if your database is badly planned and designed. A poorly designed table can be repaired through global editing or adding or modifying fields.

    Unfortunately, wholesale modification to one table can lead to unexpected disasters, as forms, queries, and reports based on the original table design no longer work as designed. Small wonder that database design professionals often include extra charges for redesign.

  • slide 6 of 7

    So look before you leap.

    Thinking about designing a new database? MS Access has a number of handy templates ready for the new user. If your needs exceed what these templates offer, however, you may need to resort to the “stubby pencil" method and design your database on paper. Consider first what you want your database to do (the reports, data retrieval and groupings, etc.). Then design your tables that will be the source of everything.

  • slide 7 of 7

    MS Access - Your tables are everything!

    Table DesignTable View

MS Access Databases

Microsoft Access is a powerful tool for database management. This article series introduces the new user to database structure. Read all about database objects and how to tailor your data use through tables, forms and queries.
  1. MS Access Database Objects
  2. MS Access Databases: Tables are the Foundation
  3. MS Access Databases: Using the Power of Forms
  4. MS Access Databases: Data Mining Through Queries
  5. Microsoft Access: The Power of Database Reports