an Access Database:
A Primer for Resource Center Managers
by
Laura C. Larsson
Creating Tables
Table of Contents
Introduction to TablesPlanning Tables
- Make a list of all the information you need
- Examples from another database
- Map it out on paper
- Fields
- Creating Fields for Your Serials Database
Modifying a Table
Assignment 1
Assignment 2
Final Comments About Tables
For More Information
Introduction to Tables
Tables are the logical structures that result from our planning the subjects of our database. The theme of our database is the collection of information about the journals in our resource centers. Now we need to plan out the subjects which are assembled to create the database structure.Planning Tables
Make a list of all the information you need.
| Assignment:
Here are several records from my ProCite database. Look at these records carefully and analyze them. (Remember that these records are taken from a flat file manager). Take 10 minutes to think about what elements are contained in this database if you use the records as an example. Write down on a piece of paper suggested table names. If you have problems thinking of names for tables, cheat, but only after you've really tried to think of names. Think of tables as subjects. What subjects would you include in a database whose major function was the management of journals? How many tables are we going to end up with? My database ended up with
18 excluding the switchboard table.
|
Examples from Another Database
If I were creating a database about cities, what might some of the subjects be? Well, I might include, Realtors, Restaurants, arts such as Concerts, Movies, Ballet, Social Services, Transportation, and so on. A city database would be VERY complex, much more complex than we could list here, but this gives you an idea.The Students and Classes database has only seven tables not including
the switchboard table:
|
Classes Instructors Results Students Students and Classes |
Map it out on paper
The instructor has provided cards for you to use for noting down which information should go in which table. Use a pencil to write the Table name at the top of each card. If you are working on the Web, round up some large cards, or tear sheets of paper in half so that you have something to write on. Everyone needs to plan out the tables on paper before then commit to table building. Planning up front will save you from tearing out your hair in frustration.Fields
Under each table name, list all of the table's characteristics.Now, under each table name, start writing in the fields you think you should be included in that table. Remember a table is like a subject.
Leave a little room on the left and right hand sides of the table - 1.5 cm (or half inch) ought to be about right - since we will be using the cards later to determine the entity relationships.
Here is a sample table from the Students and Classes database.
| If you need help, look at the hints for fields pages. |
Creating a New Table
To create a new table, click on the Table tab on the Database window, and then click the New button.
The New Table window will appear.
We are going to use Design View. Click the Design View line in the list of possibilities and click OK.
The explanation of why we picked the design view is in the table below.
| Option | Description |
| Datasheet View | Places you in the datasheet view with predefined fields. With Datasheet view you can enter data directly into the table. |
| Design View | Allows you to create the necessary fields, allowing for different options to be set (name, data type, etc). Since we know what fields we want, and since we want the experience, we'll use design view. |
| Table Wizard | Leads you through laid out steps that assist you to develop the new table. As with the other Wizards, this is a very useful feature for helping you design tables. |
| Import Table | Import information in to the database and sets up the database according to the fields in the imported file. If you have an existing database, such as a contact database, you can grab the address and phone number table and (most likely) use as is. It's worth downloading several of the Access databases from their Website, or opening up the databases which came with the program. |
| Link Table | Link to a table that exists in a different database. Until you feel very very comfortable with Access, I wouldn't try it. This is an advanced technique. |
The following table will appear. This is what you
will use to create the first table.
| Item | Description |
| Field Name | Name of the field. Use spaces if you wish. I chose not to. |
| Data Type | Select the type of data that is to be stored (see next table for explanations). Can be things like text, numbers, data/time, memo, currency, etc. |
| Description | A description of the field. The description field is optional. However, if you do use it it will appear in the status bar (bottom left of the screen) when the user is in that field on a form or in the datasheet view. |
| Primary Key | Sets the field to be a primary key . The Primary Key ensures that no
two records in a database contain the same value for that field. Only one
primary key is allowed per table.
Access can assign the Primary Key for you if you want. The Primary Key is marked with a key symbol in the Row Selector column. The Primary Key assigned by Access is usually an automatically generated sequential number. |
| Field Size | Allows you to set the size of the field. For example, you can limit text entries such as first names to 20 characters. |
| Format | Format in which to display the values for the field to the users. Access has preset Zip codes for example to display in a certain format with the space in the middle (Zip space four characters or 98053 7408) . |
| Decimal Places | Set the number of decimal places to show for a numeric field (usually 2). |
| Input Mask | Restricts the data that the user can enter. Used to ensure correct data entry. i.e. for a postal code, you want to make sure that the all characters entered are numbers. Please note that different countries have different combinations of numbers and characters. Canadian postal codes begin with a letter rather than a number, for example. |
| Caption | If you want a different name to appear for the column header in the datasheet view or the label in form view. You may have labelled the original field "LogicalJournalName " but you want the label to say "Journal Name" |
| Default Value | A value to be entered in to the table if the user does not enter one. This is important if you are running a survey and the survey respondent has not answered that question. The default value is often 99 since that number is often not used in answering questions in a survey. |
| Validation Rule | Can be used to make sure the information being entered is within a range (i.e., validate the information the user enters. You may wish to make sure the value is always less than $200) |
| Validation Text | Allows you to set the message that appears if the validation rule fails. If nothing is entered for this value, Access will display a default message. The message might give advice on what to do next. |
| Required | If turned on, the user must enter something in this field in order to save the record. |
Creating Fields for Your Serials Database
We have already decided we need 18 tables. To enter a field name, click on the first available field. Type in the first field name. In the Data Type field type in what kind of data you'll be entering. Here is a table with a list of options.More About the Description Field
It's a good idea to add a comment in the Description Field. Information in the Description Field appears in the bottom left hand side below the scroll bar on your Access window. It can be very helpful for someone doing data entry for the first time. The Description Field acts like a data dictionary.Common Table Design Problems
We'll just mention a few here.Modifying a Table
With Access you can add, rename, delete and move fields. If you delete a field, you must also delete the field from forms, queries, and reports. Similarly, if you rename a field, it must also be changed in other tables if it is a foreign key, and in forms, queries, and reports.Changing data types after the fact is fraught with problems. If you change a data type from a number to currency, and the information is longer than what is allowed for currency, you may lose some of your numbers through truncation of some of the digits. If you reduce the number of characters in the field size, and you've miscalculated, some characters can be truncated and lost (but only if you've entered data without testing the data first for longest length of field).
Adding a new field to a database at the end of the table is easy. Simply
click the last line at the end of the table and start typing. To insert
a field in the middle of the table, click on the field immediately below
where you want the new field to appear and click on Insert | Rows.
You can then enter the field and its characteristics.
I always end up adding way too many foreign keys to my tables and end up having to delete them after I've confirmed the relationships between the tables.
To delete a field, click on the field selector (the first column to
the left) and press Delete. Alternatively, you can select Edit |
Delete Rows from the Menu bar.
| Assignment 2:
Use the examples we discussed to create 18 tables for the database.
Do as many as you have time for in today's session since we will use many
of them to create queries.
|
Saving Tables
Use File | Save or the Save button to save each table. Use the same table names as I've used and click OK.Final Comments About Tables
Once you have defined a table's structure you can add records to that table. Furthermore, you can modify the structure of the table. You can add new fields, delete fields, re-order fields even if you've already entered data. But you do want to be careful if you do. Accidently deleting data is no fun. Think twice before making changes - unless you are working in a practice database.For More Information
Look at Creating, Importing, and Linking Tables in Access Help.Next: Building Relationships
Back: Creating Tables