Access, like most other Windows programs, is started by double-clicking on its icon in the Program Manager. Access is part of a suite of applications produced by Microsoft called Microsoft Office, so the icon is usually found in the Microsoft Office group of icons. Once in Access, the user will be presented with a blank Access screen.

Access is waiting for the user to either open an existing database or create a new database. During this course the reader will be guided through the creation of a database, so it is desired to start a new database file.

Starting a New Database

In Windows programs, the option for creating a new file is usually in the File menu on the menu bar, and Access is no exception. The following steps are used to create a new database.

1. Click on the File option on the menu bar.

2. Click on the New option in the File pull-down menu

3. In the New Database dialog box, choose the desired drive and directory from the lists for creating the new database file and type a meaningful name for the database in the File Name blank. Click on the OK button.

The Access Interface

Access has some parts of its interface that are unique or are peculiar to the Microsoft Office suite of applications of which Access is a part. These attributes are: the Database Window, the Menu Bar, the Tool Bar, and the Status Bar.

The Database Window

Once the new database file is created, the user is presented with an empty Database window.

This window looks like a tabbed notebook. Each tab is for each part of the database: Tables, Queries, Forms, Reports, Macros, and Modules. Each page is for a list of members of each part. At the top of the Database window are three buttons: New, Open (except for Reports, when the button is labeled Preview), and Design. The New button is used to create items in each of the pages. Open (Preview for Reports) is used to open the highlighted item in the current page. Design is used to modify the design of the highlighted item on the current page.

The Menu Bar

Most Windows applications have a menu bar. The menu bar is the horizontal list of options below the title bar of the Access window. All Access-specific functions can be found in the menu bar. Depending on what is currently active (or alternatively, has the focus) in the Access window, different options are available. For example, if the currently active window is a form design window, there will be different options available than when the currently active window is an open table. It is customary to describe the series of choices made in a menu bar by writing the choices separated by the pipe (|) character, and this convention will be used in this seminar. For instance, clicking on the File menu bar option and then choosing the Open option from the File pull-down menu would be written File|Open.

The Tool Bar

Also known as a button bar, the tool bar resides directly below the menu bar. It is a series of buttons that can be clicked on with the mouse to perform actions more quickly. All the functions available on the tool bar are also available on the menu bar, but they are more readily accessed from the tool bar because fewer clicks are needed to perform them. The tool bar can be hidden, so if it is not visible,

1. Choose View|Toolbars... from the menu bar.
2. Choose the tool bars that you want to be visible from the resulting Toolbars dialog box, and
3. Click the Close button in the same dialog box.

The Status Bar

The Status bar is at the bottom of the Access window. It is a horizontal bar that is used as an area for displaying information about the active object in the Access window. For example, the status bar is used to display prompts that explain what type of information is expected to be entered into blanks on a form.

Opening the Example Database

For this course, there is an example database which will be used for illustrating concepts in using Access. It is a simple database that keeps track of student enrollment, and it is on the course diskette in the file COURSES.MDB. To open the database,

1. Click the open button on the toolbar or choose File|Open from the menu bar,
2. Choose the A: drive from the drives list at the bottom of the Open dialog box, and
3. Double-click on the COURSES.MDB entry in the list of available filenames below the Filename blank in the Open dialog box or click once on the COURSES.MDB entry and then click the OK button.

Tables

Tables are where the raw information in the database is stored. Each table contains information about a particular subject. To see what tables are in a database, the Table tab must be active in the Database window. In example database, which should be open, there is one table already created called Courses. There are two ways to look at a table in Access, or to use Access terminology, two views of a table, datasheet view and design view. To look at a table in datasheet view,

1. Click the Table tab of the Database window,
2. Click on the desired table in the list of available tables to highlight it, and
3. Click on the Open button.

To look at the design of a table,

1. Click the Table tab of the Database window,
2. Click on the desired table in the list of available tables to highlight it, and
3. Click on the Design button.

1. Click the Table tab of the Database window,
2. Click on the New button.

The following sections entitled Looking at the Information in a Table (the Datasheet View) and Looking at the Design of a Table (the Design View) describe these views in detail.

Looking at the Information in a Table (the Datasheet View)

In datasheet view, the information in the table is displayed in a grid (similar to looking at information in a spreadsheet application like Microsoft Excel) where the rows represent the records and the columns represent the fields of each record. Along the top of the table in datasheet view are the column buttons with the name of each field on each one.

The column buttons can be used to select one or more columns at a time by clicking on a column button or by clicking and dragging across a series of column buttons. Along the left side of the table in datasheet view are the row buttons. The row buttons, in addition to facilitating the selection of one or more records in similar fashion to the column buttons, indicate whether a record is selected , being edited , or is the new, blank record Information can be added or removed from the table when the table is in datasheet view.

Adding Records in Datasheet View

The last record in the table in datasheet view is a blank record into which a new record can be entered into the table. The new record has the new record indicator in its row button. Information can be directly entered into the fields in the row. The information in the new record is automatically saved to the disk file when a different record becomes active by clicking the mouse in another record (row), by pressing the Tab or Enter key in the last field of the record, or by using the arrow keys to leave the record.

Try It:

Enter a new record into the Courses table. Put what follows the * symbol into

the corresponding field (named by the underlined text). To move between

fields use the Tab key, arrow keys or mouse.

WCUDeptID * ENG

WCUCourseNumber * 100

WCUCourseSection * 10

WCUCourseName * English Literature

Credit_Hours * 3

Course Instructor * English, Dr. Margaret T.

Modifying Information in a Record in Datasheet View

The information in an existing record can be modified in datasheet view. The information is edited directly by first putting the cursor into the desired field to be changed using the mouse or the arrow keys and then entering the changes from the keyboard. The changes are automatically saved to the disk file when a different record becomes active by clicking the mouse in another record (row), by pressing the Tab or Enter key in the last field of the record, or by using the arrow keys to leave the record.

Try It:

Change the contents of the following fields in the previously-added record.

Credit_Hours * 4

Course Instructor * English, Dr. Maggie T.

Designing a Table (Design View)

Designing a table is a matter of naming the fields and describing what type of information will be put in each field. Breaking up an database problem into easily manageable chunks of related information (tables) and then further breaking tables into their constituent parts (records of related fields) can be a difficult task depending on how complex the problem is. Whole semester-long courses tackle the problem of database design, so it is far too large a topic to cover in this beginning level seminar. This course will, however, step through the design of a very simple database problem which will hopefully give some insight into the design of databases. The design of an existing table (Courses) in the sample database will be discussed initially to illustrate what is needed in the design of a table. After that, a new table will be designed (Enrollment) that will be used to augment the discussion of the design of tables.

Viewing the Design of an Existing Table (Design View)

To view the design of an existing table,

1. Click on the Table tab of the Database window,
2. Click on the desired table in the list of available tables to highlight it, and
3. Click on the Design button.

Try It:

Perform the above steps to open the Courses table in the example database in

design view.

Examining Design of the Courses Table

The design view of the Courses table in the example database will be examined to pick apart the components of a table’s design. The design view window is divided into two parts. The upper part contains the list of field names, their respective data types, and any helpful descriptions of the fields. As in datasheet view the rows of the list of fields have buttons to their left for easy selection of one or more rows. If one or more rows have key indicators, , next to them, the fields that the rows represent are the key fields (or field if there is only one). The bottom part lists the properties for the currently-selected field. The properties will change depending on what the data type of the current field is.

Field Names

Field names can be up to 64 characters long and be composed of any combination of letters, numbers, spaces, and special characters except for the period (.), exclamation point (!), back quote (`), and square brackets ([]). Field names cannot start with spaces or control characters (ASCII characters 0 through 32). [Search in Access Help for 'standard naming conventions'] Field names should be meaningful so that it is easy for everyone who views the design to understand why each field is there.

Data Types

A field’s data type is a description of what type of information will be stored in the field. Data types are important for a couple reasons:

1. The data type indicates the maximum size of the information entered into the field so that Access knows how much room to allocate for it. If this was not known, Access would have to make allowances for all the data in the fields to be of maximum possible length which would introduce unwanted complexity and slowness of operations, such as sorting.
2. Knowing the data type ahead of time allows Access to catch certain data entry errors before they cause worse problems.

Field Descriptions

The Description blank is used to further explain what is expected to be entered into a particular field. Also, the description will appear in the status bar when the particular field is the active one in datasheet view, or when a control on a form that represents the field is active. Controls and forms will be discussed in a later section, so it is not imperative that they are understood at this time.

Courses Table Design (Example)

The Courses table has the following fields in it:

• CourseIDis of a special data type called Counter. It is the key field or field that contains a value to uniquely identify the record. This is signified by the key indicator,, in the row button. The values of a field of Counter type are supplied by Access and are guaranteed to be unique and are good for identification of records. Counter fields start out with the value 1 and each subsequent record gets a counter value incremented by 1. There is a limit to the value of a counter field, approximately 4 billion, so there is a limit to the number of records in a table, but the practical limits of available disk space will be reached before a counter field runs out of values.
• WCUDeptID is a Text field of maximum length 5. The Text data type was chosen because alphabetic characters will be stored in this field. The length 5 was chosen because the values of this field will be abbreviations. Access is told that a value is required in this field by setting the Required property to Yes.
• WCUCourseNumber is a Text field of maximum length 5. The Text data type was chosen because it is desirable for leading zeros to be retained. A value is required in this field.
• WCUCourseSection is a Text field of maximum length 5. The Text data type was chosen because it is desirable for leading zeros to be retained. A value is required in this field.
• WCUCourseName is a Text field of maximum length 50. The Text data type was chosen because alphabetic characters will be stored in this field. A value is required in this field.
• Credit_Hours is a field with a data type of Number. Its FieldSize property is Byte which means that it can store whole number values from 0 to 255. This data type and size were chosen because only small positive numbers will be needed. A value is required in this field.
• Course Instructor is a Text field of maximum size 50. The Text data type was chosen because alphabetic characters will be stored in this field. A value is required in this field.

Possible Field Data Types

The following is a complete list of data types for fields [this information was found in the Access Help facility; search under "data types: field"]:

1. Text - This is the default data type for a field. Up to 255 characters or the length set in the FieldSize property can be stored, whichever is less.
2. Memo - Text with a maximum length of 64,000 bytes (characters). Fields of type Memo cannot be indexed (sorted).
3. Number - Any numeric type. The maximum precision and size of the number that can be stored in the field is controlled by the value in the FieldSize property. If the FieldSize property has a value of

a. Byte - it can store whole numbers from 0 to 255 and it occupies one byte,

b. Integer - it can store whole numbers from -32,768 to 32,767 and it occupies two bytes,

c. Long Integer - it can store whole numbers from -2,147,483,648 to 2,147,483,647 and it occupies 4 bytes,

d. Single - it can store numbers with six digits of precision from -3.402823x10^38 to 3.402823x10^38 and it occupies 4 bytes, and

e. Double - it can store numbers with 10 digits of precision, from -1.79769313486232x10^308 to 1.79769313486232x10^308 and it occupies 8 bytes. This is the default FieldSize value when a Number data type is chosen.

4. Date/Time - Date and time values for years from 100 to 9999.
5. Currency - Accurate to 15 digits on the left side of the decimal point and 4 digits to the right.
6. Counter - When a new record is added, Access automatically increments its value. Its intended use is for generating unique identifiers for records called keys.
7. Yes/No - Good for fields that only need to have two values. It can represent concepts like On/Off, True/False, etc. Fields of Yes/No type cannot be indexed (sorted).
8. OLE Object - An Object Linking and Embedding standard compliant object such as a Microsoft Excel spreadsheet or Microsoft Draw object. These objects are limited in size to about one gigabyte (approximately one billion bytes). OLE objects cannot be indexed (sorted).

Designing the Enrollment Table in the Example Database

The example database is intended to hold information about courses and students that are enrolled in the courses. In its current form, the database is only keeping track of information about the courses in the table called Courses. Information about the students enrolled in the courses will be kept in another table called Enrollment. Obviously, information about the student (Name, University ID Number, etc.) that is enrolled in a class must be kept, but some way must be found to link (or relate) an Enrollment record to a Courses record. The key field, CourseID, is useful for that because it uniquely identifies a Courses record. So the CourseID value of the desired Courses record is placed in a field, also called CourseID, in the Enrollment record.

With this in mind, the Enrollment table will be designed with the following fields and data types:

1. CourseID
   a) Data Type: Number
   b) Description: The course identifier for this enrollment record
   c) FieldSize: Long Integer
   d) Required: Yes

   StudentLastName

   a) Data Type: Text

   b) Description: The student’s last name

   c) FieldSize: 32

   d) Required: Yes

   StudentFirstName

   a) Data Type: Text

   b) Description: The student’s first name

   c) FieldSize: 16

   d) Required: Yes

   StudentMI

   a) Data Type: Text

   b) Description: The student’s middle initial

   c) FieldSize: 1

   d) Required: No

   StudentID

   a) Data Type: Text

   b) Description: The student’s WCU ID number

   c) FieldSize: 6

   d) Required: Yes

Try It:

Design a new table using the above definitions. To start a new design:

1. Click on the Table tab of the Database window,

   Click on the New button at the top of the Database window,

   Click on the New Table button in the resulting New Table dialog

   box.

When finished entering the design information into the design view window,

do the following to save the design.

1. Close the window by double-clicking on the control menu button at the
   upper left corner of the window.

   Access will ask if any changes need saving. Answering Yes will result in a

   Save As dialog box asking for a name for the new table.

   Enter Enrollment and click on the OK button.

   Access will indicate that no fields in the design have been designated as

   the key. A key is not needed in this design because it is not necessary to

   uniquely identify a record, but if one were desired, a key could be assigned

   by first selecting the key field(s) using the row buttons and then clicking on

   the Key Button, , on the toolbar. Answer No to the dialog box.

Relationships

In a relational database, tables can be related or linked to each other the value of a field or set of fields. Access provides a way to describe these linkages so that it can keep track of them and automate the combining of information in related tables in forms and queries.

Relationship Types

Relationships are usually classified by considering how many records in one table are related to one or more records in another table. When one record is related to one and only one record in another table, the relationship is called a one-to-one relationship. One-to-one relationships are not really useful because records from the first table can be combined with their corresponding related records in the other table to form a single, larger table with the same number of records. The most common relationship type is when one record from a table relates to one or more records in another table. This type of relationship is called a one-to-many relationship. The third logical possibility in this taxonomy of relationships is when one or more records in a table relate to one or more records in another table, which is called a many-to-many relationship. While many-to-many relationships may have a few more uses than one-to-one relationships, the complexity involved with using them is prohibitive.

One-to-many relationships are by far the most frequently used. A one-to-many relationship exists in the example database because for each course record in the Courses table there can be multiple related records in the Enrollment table, representing each student that is enrolled in the course. This relationship will be used to illustrate how relationships are defined in an Access database.

Defining a Relationship in the Example Database

Defining the relationship between the Courses table and the Enrollment table is done using the following steps:
1. With the Database window as the active window, choose Edit|Relationships from the menu bar. The empty Relationships window will appear.
   

2. Click on the Add Tables button on the toolbar. The Add Table dialog box will appear.
   

3. Choose Courses from the list of tables and click the Add button. A list of all Courses’ fields will appear in the previously empty Relationships window.
   

4. Choose Enrollment from the list of tables and click the Add button. A list of all Enrollment’s fields will appear in the Relationships window.
5. Click on the Close button in the Add Table dialog box to get rid of it.
6. Click on the CourseID field in Courses’ list of fields and drag it over to the CourseID field in Enrollment’s list of fields. To define a one-to-many relationship as is being done here, one drags from the one table to the many table. A Relationships dialog box will appear.
   

7. Click on the Referential Integrity check box to put an x in it. Referential Integrity is a facility in Access where assures that there is always a record in the 'one' table of the relationship for every record in the 'many' table. In this example, it will assure that for every Enrollment record there exists a Courses record that relates to it.
8. Click on the Many radio button in order to indicate that this is a one-to-many relationship.
9. Click on both the Cascade Update Related Fields and the Cascade Delete Related Records check boxes. The first check box assures that if the key value is changed in a record on the 'one' side of the relationship, all of its related records on the 'many' side will have their related field value changed to the new value. The second check box assured that if a record is deleted on the 'one' side of the relationship, all of its related records on the 'many' side will also be deleted. This automatically forces strict adherence to referential integrity.
   

10. Click on the Create button. The Relationships dialog box will go away and a line adjoining the two related fields will appear with a numeral 1 on the 'one' side of the relationship and an infinity symbol on the 'many' side.
    

11. Close the Relationships window by double-clicking on the control menu button and answer Yes to saving the layout changes to the Relationships window.

Forms

Entering information directly into a table in datasheet view, while serviceable, can be error-prone and not very enjoyable. Forms are on-screen windows with blanks and other data input objects, such as check boxes, radio buttons, pick list, etc. (collectively known as controls), that are useful for easing the data entry task. Forms also help reduce data entry error by restricting values in fields to certain expected or meaningful values.

Designing forms from scratch can be a tedious and difficult job. Each control has many properties with many possible values, making form design by hand an unattractive proposition for anything other than the most simple forms. That is why Microsoft developed Wizards, on-screen help facilities that guide a user through some of the more difficult tasks in an application. This seminar has not covered Wizards up to this point because most of the tasks that have been covered are simple enough to tackle without their help. Wizards are available for helping with just about every facet of an Access database including table design. The Form design Wizard will be used to construct a form for the example database.

Course Information Input Form Example

It would be convenient from a data entry standpoint to have a form for inputting both course and enrollment information at the same time because the information is related. This is completely possible and will be accomplished using the Access Form Wizard. Access will use the relationship between the Courses and Enrollment tables to do this almost automatically.

Because of the one-to-many relationship between the Courses and Enrollment tables, Access can create a Form for the records from the 'one' side of the relationship (Courses) with an embedded sub-form that will list the related 'many' records (Enrollment records). This will become clear after the form has been created.

The following instructions will illustrate the creation of a Form using the Form Wizard.

1. Click on the Form tab of the Database window to make the Form page active
   

2. Click on the New button in the Database window.
3. In the resulting New Form dialog box, select the Courses table from the pull-down list labeled Select a Table/Query:
   

4. Click on theForm Wizards button in the New Form dialog box.
5. Choose Main/Subform form the resulting Form Wizards dialog box and click the OK button.
   

6. The Main/Subform Wizard will ask which table contains the records for the sub-form. Choose Enrollment from the list and click the Next > button.
   

7. The Main/Subform Wizard will ask for which fields from the Courses table should be on the main form. Click on the >> button to put all the fields onto the main form and click the Next > button to proceed.
   

8. The Main/Subform Wizard will ask for which fields from the Enrollment table should be on the sub-form. Click on the >> button to put all the fields onto the sub-form and click the Next > button to proceed.
   

9. The Main/Subform Wizard will ask for the user to pick which visual style is desired for the form. Click on the Next > button to accept the default style and proceed.
   

10. The Main/Subform Wizard will ask for a title for the form. Click on the Finish button to accept the defaults.
    

11. A message will appear indicating that the sub-form must be saved before proceeding. Click on the OK button.
    

12. In the resulting Save As dialog box, enter CoursesSubForm and click on the OK button. The completed form will appear on the screen, ready for data entry.
    

13. To save the form, close it by double-clicking on the control menu button, answer Yes to the "Save changes..." dialog box, enter CoursesForm in the Save As dialog box, and click the OK button.

Opening Existing Form for Data Entry

To open an existing for to use it for data entry follow these steps:

1. Click on the Form tab of the Database window.
2. Choose the desired form from the list of available forms.
3. Click on the Open button in the Database window. The desired form will then open.