Le Moyne College CSC-151 Microsoft Access 2003 Reference

Table design view

When creating a new Access database, the first thing you should do is create one or more table objects. As shown in the picture to the right, clicking on the Table object type displays three methods for creating a new table object: Create table in Design view Create table by using wizard Create table by entering data In this class, we will always use Design view to create a table. The easiest way to create a table using design view is to double-click on the "Create table in Design view" entry. You may also click on the "New" button (see ) to display the "New Table" dialog box. You would then select "Design view" and click the "OK" button. In either case, a new child window is shown that is called the table design view:
	The table design view contains two sections. Top section - lists the field definitions. Bottom section - lists the field properties for the selected field. In the picture to the left, the table design view is empty because we are creating a new table object in the database. What is a field in a table? A field represents a distinct piece of data associated with a real or imaginary entity. For example, let's say we want to store data about a student. We may want to know a student's name, address, phone number, and major. So each of these are candidates for becoming a field in a student table. This seems simple enough. However, things are not as easy as they appear. How do we identify the fields that we need in a table? Let's say that we want to create a field for a student's name. Do we create one field for the entire name? Do we create one field for the first name and a second field for last name? Do we want to know the student's middle name?
We have similar issues when we think about a student's address. That is, there are many distinct pieces of information associated with an address - street address, city, state, zip code. For student's living in an appartment or dormitory, you would also have an appartment number or room number.
How do we create a field definition in a table? So let's assume that we want to have two fields for a student's name - first name and last name - and that we do not care about a students middle name. The following steps are used to create the first field definition: Click on the cell immediately below the Field Name column title (i.e., look for the small black arrow pointing to the first field definition row). Type in the name for your field. In this example, we would type:  First Name Strike the Tab key on the keyboard to advance the cursor to the Data Type column. As you can see, the Data Type entry now says  Text  and the Field Properties section displays may property values. Since the  First Name  field will contain text, we do not need to change the Data Type value. Strike the Tab key on the keyboard to advance the cursor to the Description column. Type in a phrase or sentence that describes the purpose of the field. The picture to the right shows what the table design view looks like immediately after you've typed in the description. (Note: at this point, the cursor is still in the Description field of the First Name field/row.
Observations at this point Field Size - the first Field Property listed - has defaulted to 50. For the Text Data Type, Field Size represents the maximum number of characters a value may contain for this field. You should decide what the maximum number of characters should be for each field whose Data Type is Text, and then change the Field Size property value for the field. Note that the maximum Field Size property value for the Text Data Type is 255. (For purposes of this demonstration, the Field Size property value will be changed to 20 for the First Name field.) See field data types for more information on the Data Type column in the field definition list. See field properties for more information on the field properties listed in the picture above. The blue text to the right of the Field Properties list provides some help text. As you tab through the Field Name, Data Type, and Description cells in the first row of the First Name field definition, you'll see this blue text display help text based on which of the three cells the cursor is in.
Create remaining fields After creating the Last Name field, and other appropriate fields used for contacting a student, the Table Design View looks like the window on the right. Notice that the Home Phone Number field is a Text field whose Field Size is 10. This allows for a 3-digit area code, 3-digit exchange, and 4-digit extension. Even though phone numbers in the U.S. are numeric, the Data Type of the Home Phone Number field is Text. Generally, you should not use a numeric data type for a field unless you need to use arithmetic operators on the field value. Since it is difficult to imagine a scenario where we would need to do arithmetic (i.e., addition, subtraction) on a phone number value, we've used the Text Data Type. Does it matter how we order the fields in the top section? The short answer is no - it does not matter how we order the fields in the Field Definition list.
Save table definition Now that we've created all of the fields for this table, we need to save the table definition. You may save the table by doing any of the following:
Clicking on the Save button or selecting Save from the File menu. In either case, the Save As dialog box (shown to the right) will appear. At this point, you type in the name for the Table (in this case - Student) and click the OK button.
Clicking on the Close button associated with the Table Design child window. At this point, Microsft Access warns you that you have not yet saved the table design (as shown to the right).
If you select the Yes button, the Save As dialog box (shown to the right) is displayed. You would then enter your table name and click the OK button. If you select the No button, your table design is not saved. (You will have to reenter all of the Field Definition and Field Property values.) If you select the Cancel button, the Table Design view remains open and you may modify this design.
Assuming that you entered a table name and clicked OK in the Save As dialog box, a warning about missing primary keys is then displayed (shown immediately below).
Here are the three options available to us: Click the Yes button Microsoft Access will save the table design using the table name you entered. In addition, Microsoft Access automatically adds a new field to your table design, and makes this new field the primary key. This new field will have a Data Type of AutoNumber. In some cases, it is appropriate to have Access automatically create a primary key field. Click the No button Microsoft Access will save the table design using the table name you entered. At this point, the table does not have a primary key. Click the Cancel button The Table Design view remains open and it has not yet been saved. For this demonstration, the No buton was clicked. At this point, the Student table design has been saved in the database file, and Microsft Access displays the Database Window (as shown on the right). Note that below the three "Create Table ..." options is the word Student. This represents the Student table that we just saved to the database file.
What is a primary key? A primary key is one or more fields whose values will uniquely identify each data row in the table. In the case of our Student table - can we identify one or more fields whose values will collectively identify each student data stored in the table? We could choose the First Name field as the primary key - but it's likely that many students would have the same first name. We could choose the Last Name field as the primary key - but it's likely that many students would have the same last name. We could choose the First Name AND Last Name fields as the primary key - but it's still likely that more than one student could have the same first and last names. We could choose the First Name, Last Name AND Street fields as the primary key - but it's still possible that more than one student could have the same first and last names and live on the same street. If this is possible, then adding City, State, or Zip Code as the fourth field in the primary key does not help us uniquely identity these two (or more) students that have the same first and last name and live on the same street. We could choose the Home Phone Number field as the primary key - but it's possible that siblings could go to the same school. We could choose the First Name, Last Name AND Home Phone Number fields as the primary key. In this case, its unlikely that two or more peolple would have the same first and last name, and the same home phone number, and go to the same school. (However, this is not outside the realm of possibility - note that George Foreman named all five of his sons George - so this is still not the best choice for the primary key. In the final analysis, it looks like we should add another field to this table - called Student ID Number - and we should make this field the primary key.
Adding a field to an existing Table To add a field to the Student table, we must first open the table in Design View. We can do this by: Selecting the table name (Student) and then clicking on the Design button . Right-clicking on the table name (in this case - Student) and then selecting Design View from the shortcut menu. The picture on the right shows the Table Design View for the Student table. We now want to add a field called Student ID Number to the field list. We could add this field to the bottom of the list. In this case we would type in the Field Name, Data type, and Description in the first empty row immediately below the last field listed. We could add this field immediately before any of the existing fields in the table. To do this, we would position the cursor in a particular field (notice that the First Name Field Name is selected in the picture to the right) and then stike the Insert key on the keyboard (or select Insert, Rows from the menu).
Let's insert this new field immediately before the Street field in the list. To the right is what the field list would look like after positioning the cursor in the Street field and striking the Insert key on the keyboard. At this point, we would type in Student ID Number in the Field Name, and then tab over to the Description field and type in an appropriate description for this new field. (For purposes of this demo database, I made the Field Size of this field 15.)
Now that we've created the Student ID Number field, we need to make this field the primary key: Move the cursor to the Student ID Number field. (Note: the small black arrow should be pointing to the Student ID Number Field Name. Click on the primary key button or select Primary Key from the Edit menu. You should see a little key symbol to the left of the Student ID Number field. Save the changes you've made to the Student table design and close the Table Design View. If you were to reopen the Table Design View for the Student table, the field list should look like the following:

For more information on the Table Design View, see field data types and field properties.

Table datasheet view

Now that the database contains the design of a table, we should enter data into this table.

From the Database window (a portion of which is shown to the right), we can display the Table Datasheet View by: Selecting the table name (Student) and then clicking on the Open button . Double-clicking on the table name (Student). Right-clicking on the table name (Student) and then selecting Open from the shortcut menu.
Performing any of the above actions results in the Table Datasheet View being displayed. The picture below illustrates what the Table Datasheet View looks like for the just created Student table. Since the Student table design was just created and saved, there is no data in the table. This is illustrated below as the Table Datasheet View is showing one empty row for the Student table.
Important The Datasheet View is used by Table, Query, and Form objects in Microsoft Access. Regardless of the Access object being used, the Datasheet View provides the same set of features and capabilities. Because of this, from this point forward, we'll use the term Datasheet View instead of Table Datasheet View when describing its features and capabilities.
To enter data using the Datasheet View, simply type text into the field where the cursor is, and then tab to the next field and type in the appropriate data. Continue this until you've entered data for one Student. Note how the Datasheet View changes immediately after you've typed the first character into the First Name field. Observe the following in the picture below. The pencil icon to the left of the first row. Microsoft Access is indicating that a row of data in the table is being modified - in this case, data for a new Student is being entered. The asterisk icon to the left of the second row. Microsoft Access will always have an empty row at the bottom of the datasheet view. This empty row is denoted with an asterisk, which indicates the place where you may enter a new row of data. After you've entered data for the last field of the table (in this case - the Home Phone Number field), striking the tab key causes the data for that row to be saved to the table. After entering three data rows representing three Students, the Datasheet View looks like the following picture. Note the following regarding the above Datasheet View showing three Student rows. The cursor is in the second row of data. This is denoted by the small black arrow to the left of the First Name field. The navigation buttons allow you to move to different rows in the Datasheet View. The button moves the cursor to the first row in the datasheet view. The button moves the cursor to the previous row in the datasheet view. The button moves the cursor to the next row in the datasheet view. The button moves the cursor to the last row that contains data in the datasheet view. The button moves the cursor to the asterisk row in the datasheet view, allowing the user to enter a new data row. The textbox indicates which row the cursor is currently in, and allows you to navigate to a specific row number. Clicking on this textbox, entering a number, and striking the enter key will move the cursor to the specified row. Note the of 3 at the end of these navigation buttons. This indicates the total number of data rows currently in the table. Since the cursor is currently in a First Name field, the status bar shows the description associated with the First Name field. As you move the cursor to the various fields, note that the Status Bar text changes to match the description of that field - as specified in the Table Design View.
Are duplicate primary key values allowed? As mentioned above, the primary key for this Student table is the Student ID Number field. We will enter a fourth data row whose Student ID Number value matches one of the existing data rows. The datasheet view looks like the following after entering the fourth data row. Note that the cursor is in the last field (Home Phone Number) in this new data row, and that the pencil icon is in this fourth row - which indicates that it has not yet been saved to the table. When the user strikes the tab key, which would advance the cursor to the asterisk row, the following message is displayed by Microsoft Access. Since the Student ID Number in this fourth row matches the Student ID Number in the first row, the data in this fourth row cannot be added to the table. That is, each data row must have a unique value for its primary key. Since the primary key in the Student table is one field, this field's value must be unique for each data row. At this point we have two chioces: We can modify the Student ID Number in the fourth row to make its value unique. Doing this will result in the fourth data row being saved to the table. We can cancel the data entry of this fourth data row by striking the Esc key on the keyboard. Doing this causes the fourth data row to disappear - the data entered into this row was not saved to the table.

Datasheet advanced features

Go here for a description of the advanced features of the Datasheet view.