Using Tables
IIHE  |  Education  |  Getting Started With Microsoft Access  |  Using Tables

This section is an introduction to using tables in Access. Open the Sample database and you should see:

If the tables tab isn't currently selected, click once on the tab labeled Tables. Let's look at the Mercy FP Admissions table. To do this, click once on the Mercy FP Admissions and then click on the Open button. Alternately, you could double click on Mercy FP Admissions:

Note: The MRN (Medical Record Number) in all the displays have been emulated and are not real MRNs. Also, in the displays, the number has been shortened to 7 digits.

Each row is called a record and represents an admission of a patient to Mercy hospital. Note that if a patient was admitted to Mercy more than once, there is one record for each admission. Each column is called a field and contains some information about the admission. The first column is the Medical Record Number of the patient. The second column has the admission date, and the third column has the discharge date. You can get a complete description of the field abbreviations on the Mercy FP Admissions Field Dictionary page.

Browsing the Table

We can scroll through the records by using the scroll bar on the right-side of the window, the down arrow key, or the Page Down key.

Also, at the bottom of the window there are some buttons for selecting records and information about the table. It also shows which record is selected and how many records there are in the table. If the table is very large, the number of records in the table may not be displayed for a considerable length of time. Here is a summary of what the buttons do:

   Go to the first record.
   Go to the previous record.
   Go to the next record.
   Go to the last record.
   Add a new record to the database.

If you know the record number that you want to go to, you can type it into the box and press Enter:

Let's take a look at the fields. There are a lot more fields in the table than you can see at once. Using the scroll bar at the bottom of the window, you can scroll through the fields. Use the bottom scroll bar to view the DRG_DESC field:

Notice that you can't see all the text in the DRG_DESC field. You can adjust the column width of a field by moving the mouse pointer to the end of the field you want to expand at the top of the window:

Notice that the point changes shape when you get it positioned correctly. It should look like a vertical line with arrows extending out the sides. You change the width of the column by dragging the mouse pointer to the right or left. A better way is to double click Double clicking adjusts the width of the column so that the long entry is the field is visible. Now try it yourself, double click at the end of the column:

Notice that all the text in the DRG_DESC field is readable now.

Sorting the Table

You can sort the table based on any field by clicking on the field and then click on the or button. They are located on the toolbar just below the menus. The button sorts the field by A to Z, while the button sorts the field in reverse order, Z to A. If the field is a text field, the letter case is ignored. Also, if a field contains 9 and 17, 17 would come before 9 because a text sort works its way through the string one character at a time and would compare the "1" and "9" only. If field contained 09 instead of 9 then they would sort the way you would expect. If the field is defined as a number, then the number would sort correctly.

Let's try sorting a field. We are interested in doing a study on older patients that have been admitted to Mercy and we need to have more than 60 admissions in the study. Scroll over so that you can see the Age field. Click on the field where it says Age. Notice that the Age column turns black. We want to sort the Age field so that the oldest are first, so click on the button (in the toolbar):

Now we need to scroll down to find the age we should use as the cut off. Scroll down until the last person that is 70 years old is shown and then click in that row:

The record counter changes to 61 at the bottom of the window. So if we study the patients that are age 70 or older, we will have enough cases for our study.

Designing Tables

You can look at the design of the table by clicking on the design, , button:

Each of the fields in the database is shown at a row (unlike the Datasheet view where each field is a column). Each field has a corresponding Data Type and Description. The description is displayed in the status bar (at the bottom of the Access window) whenever you select this field in a query in Datasheet view or in a form in Form view or Datasheet view.

If you click in the Data Type column for MRN and then click on the pop-up menu, you see:

Here you can pick the Data Type to use for the field. Here is a description of the Data Types:

Data Type	Description
Text	Alphanumeric data. Up to 255 characters.
Memo	Alphanumeric data. Up to 64,000 characters.
Number	Numeric data.
Date/Time	Dates and times.
Currency	Monetary data, stored with 4 decimal places of precision.
AutoNumber	Unique value generated by Access for each new record. Basically, this only used for generating an index.
Yes/No	Boolean (true/false or yes/no) data.
OLE Object	Pictures, graphs, or other OLE objects from another Windows-based application.
Hyperlink	A link to a document or file on the World Wide Web.
Lookup Wizard...	Gets a value from another table or list that you enter.

The main types that you will work with are Text, Number, Date/Time, Currency, and Yes/No.

Although the MRN is a number, we've defined the field to be Text. We've done this because some MRNs have leading zeros and if the data is exported to Epi Info, it doesn't work correctly with the MRNs.

Each field also has a set of properties associated with it:

Property	Description
Field Size	You can specify the length of Text and Number fields. Text can be from 0 through 255 characters long, with a default length of 50 characters. See the Help file for Access for more details.
Format	You can control how your data is displayed or printed. The format options vary by data type.
Input Mask	You can specify an editing mask that the user sees while entering data in the field for Text, Number (except Replication ID), Date/Time, and Currency data types.. For example, you can have a date field formatted like (__/__/__) or you can have a U.S. phone number formatted like (###) 000-0000.
Caption	You can enter a more fully descriptive field name that Access displays in form labels and in report headings.
Default Value	You can specify a default value for all data types except AutoNumber, Replication ID, and OLE Object. For numbers, the default value is 0. For the Yes/No data type, False is the default value. Access provides a Null default value for Text and Memo data types.
Validation Rule	You can supply an expression that must be true whenever you enter or change data in this field. For example, <100 specifies that a number must be less than 100. You can also check for one of a series of values. For example, you can check for a list of valid doctors by specifying "Reed" Or "Kildare" or "Welby".
Validation Text	You can have text displayed whenever the data entered does not pass your validation rule.
Required	If this field must be entered, set this to Yes.
Allow Zero Length	For Text and Memo fields, you can set the field equal to a zero-length string (""). We recommend you set this to No.
Indexed	An index can be built to speed access to data values for Text, Number, Date/Time, Currency, and AutoNumber data types. You can also require that the values in the indexed field always be unique for the entire table.

By using these properties, you can control what is entered in a field and how it is displayed.

 

Introducing Databases  |  Getting Started With Microsoft Access  |  Working with the Supplied Forms