ACCESS Assignment

Document Sample
ACCESS Assignment Powered By Docstoc
					       Building a Problem Log Database in MS Access – 10%

In this assignment, you will develop a Microsoft Access database application for problem
reporting and tracking.

The database structure will consist of three different entities:

1. Employees - these are the people who are responsible for handling the problems as
   they arise.
2. Problems - these are incidents, malfunctions, bugs, performance difficulties, etc. that
   are reported by the users.
3. Actions - these are the procedures that the employees use as they attempt to resolve
   the problems.

The relationships between these entities is illustrated below:

     Employees                         Problems                           Actions
                  1                n                 1               n

Thus, we will be assuming that an employee may work on several problems, but each
problem is worked on by only a single employee. Likewise, a problem may have several
actions associated with it, but each action deals with a single problem. This is the classic
one-to-many relationship found in most multi-table database structures. It is possible to
implement many-to-many relationships in Microsoft Access, but it is more complicated,
and we will not be doing it in this exercise.

In this exercise, you will gain experience doing the following:

1.   Creating and editing a MS Access Database
2.   Building data tables, entering fields, and identifying primary keys
3.   Establishing one-to-many relationships between tables
4.   Creating standard queries in MS Access
5.   Creating and customizing forms for manipulating records
6.   Creating and customizing forms for overall control of your application
7.   Creating reports
8.   Creating a macro that allows you to run your application directly from the Windows
     Desktop or Start Menu.

Step 1: Creating the Database
   Start up MS Access. Depending on your computer’s settings, this may be done in
    either of the following ways:
     From the top MS Office Toolbar, click the MS Access button (with the picture of
        a key).
     OR, from the Start Button, find the MS Access application an run it
     OR, from the Start button, choose the Run option and type in MSAccess
   You may be prompted to with an option to create a new database. If so, select “Blank
    database” as your option. Otherwise, select File/New Database to initially create your
    database, and select Blank Database as your option.
   The File New Database Dialog will appear. Select a name for your database (such as
    Problem Log).
   Your database has now been created.
   A Database Window now appears with an “Objects Bar” on the left for Tables,
    Queries, Forms, Reports, Pages, Macro, and Module. From now on, whenever you
    enter MS Access and open your database, you will be presented with this window.
Step 2: Create the Employee Table

   Make sure that Tables is selected from the Objects Bar. This is the default setting in
    the Database Dialog.
   For this table, we will use the Table wizard. Double-click on “Create table by using
    wizard” in the database window. A wizard will take you through the steps. There is a
    sample employees table that you can use. If you use this, you will have several extra
    fields that are not necessary for our exercise. Select only the fields listed below.
         Employee ID - this is the primary key, and should be of type AutoNumber.
             AutoNumber is an integer type that simply increments each time a new record
             is added to the table.
         Last Name - should be of type Text
         First Name - also of type Text
   In the last step of the wizard, choose to modify the table design. This enables you to
    define the fields. After you have reviewed the fields you can close the Table Design
   You may now want to enter some data into the table, so click the Open toolbar button
    at the top of the Database window. You are presented with the DataSheet View, from
    which you can enter data. Don’t enter data into the Employee ID field; this is
    automatically incremented (as a AutoNumber field). The Last Name and First Name
    fields, however, should be filled. Enter five or six employee records.

   When done, close this view.
Step 3: Build an Alphabetized Employee List Query

Currently this table, if you view it from the Table tab, will be shown in Employee ID
sequence. We will want to be displaying employees in alphabetical order based on their
last names, then first names. So we will create a special query to handle this.

   Switch to Queries in the Objects Bar
   Don’t use the Wizard , just pick “Create query in design view” to create a new query
   You are now in the Query Design View. A Show Table Dialog asks you to add a
    table. Of course, you only have one choice, so pick the Employee table. It will appear
    at the top of the Design View. Close the Show Table dialog.
   On the bottom half of the view are several columns into which you can put your
    desired fields. For our purposes, the fields we want to display are Last Name,
    followed by First Name, followed by Employee ID. We want it sorted in ascending
    sequence by last name, and then by first name, so indicate this.

   When you are finished, exit the query view. When prompted, save it with the name
    “Alphabetical Employee List”.
   Now, select this query in the database window, then click on Open. This will bring
    you into the DataSheet View, but now the records are shown in alphabetical order, not
    Employee ID order.
   Note that you can change and enter data in this mode as well.

Step 4: Create the Problem Log Table

Go back to the Tables tab, and click New to create a new table for holding problem log
data. This time, however, do not use the Table Wizard, just start with a table in the
Design View.

The important field here are as follows:

Problem ID                                    AutoNumber (Should be a Primary Key). To make
                                                         This the primary key, select the field
                                                         And then click the key symbol on the
                                                         toolbar at the top. A key symbol will
                                                         appear at the left of the field.
Reported By                                   Text
Problem Category                              Text       Allowable values are: “hardware”,
                                                         “software”, “network”, and “data”
                                                         Use the Validation rule property to
                                                                  enforce this1.
Severity Code                                 Number     An integer between 1 and 5
                                                         Use the Validation rule property to
                                                                  enforce this.
Date and Time of Incident                     Date/Time
Description                                   Memo       Note: a memo field is a variable-
                                                         length text field. This is preferable
                                                         to setting a very large fixed-length
                                                         field size.
Assigned Employee                             Number     Note: this field will be used to
                                                         establish the relationship between
                                                         the employee table and the problem
                                                         log table. Thus, it must be of a
                                                         compatible type as the AutoNumber
                                                         type defined for Employee ID.
                                                         To do this, in the Field Size property,
                                                         select long integer as the field size.
Status of the Problem                         Text       Allowable values are “pending” and

  The validation rule is one of the properties of a field. Properties identify characteristics of the field. In
particular, the validation rule property allows you to constrain the allowable values. The figure below shows
the validation rule for the Problem Category field.
                                                     Use the Validation rule property to
                                                             enforce this.

After you are finished, save this table as “Problem Log”.

Step 5: Set up the Relationship Between Employees and Problem Logs

As mentioned above, there is a one-to-many relationship between employees and
problems. To do this, from the Tools menu, select Relationships. (There is also a
relationship toolbar button).

   You will be presented with the Show Table dialog, from which you should select both
    the Employees and Problem Log tables to add. After that, both will appear in the
    Relationships view.
   You will now set up a one-to-many relationship between the primary table
    (Employees) and the secondary table (Problem Log). The idea here is that one
    employee may be working on many problems, but that each problem is being handled
    by a single employee.To create the relationship, click on the Employee ID field of the
    Employees table and drag to the Assigned Employee field of the Problem Log table.
   Make sure to enforce referential integrity, and make it a one-to-many relationship.
   When finished, close the relationship window.

Step 6: Creating the Action Table and Relating it to the Problem Log

As described above, the action table indicates which actions were taken to resolve the
problem, and when they occurred. The table must have a link to the Problem Log. There
is a one-to-many relationship between Problems and Actions. Thus, this table requires the
following fields:

Action ID                             AutoNumber                    Primary Key
Problem ID                            Number                        Long Integer
Action Date and Time                  Date/Time
Description                           Memo
After you have completed creating the table, you can create a one-to-many relationship
from Problem Log to Actions by linking the Problem ID field of Problem Log to the
Problem ID field of Action. Again, make sure to enforce referential integrity.

Step 7: Enter Some Data

Now that you have created all your tables and relationships, you should take the time to
enter some initial data into the Problem Log and Actions tables. While entering the data,
you can check to see if your validation rules are being enforced. Try entering invalid
problem categories, severity codes, and problem status’s. MS Access should not allow
you to do this. Also, you can check to see if the referential integrity is being enforced. For
example, you should not be able to put employee IDs in the problem log if these
employees do not exist in the Employee table. Likewise, the Problem ID field of the
Actions table will not permit values unless they correspond with actual Problems in the
Problem Log. Also, you cannot delete an employee from the employee table if there are
problems associated with that employee.

After you have entered five or six problems, with a few actions, go on to step 8.

Step 8: Play around with the Queries

You can now do queries on multiple tables. For example, you can query to see all the
problems assigned to each employee, together with the actions that the employee took. To
do this, add all three tables to the query, and then select fields from any of the tables that
you want to use. Play around with the queries for awhile to see what you can come up

Step 9: Building an initial, simple Problem Log Form

Now that the overall database structure has been developed, we’d like to create a GUI
(graphic user interface) front end to the database. Microsoft Access supports this through
the use of Forms. We will now build a form for the Problem Log Table.

   In the Database view, Forms from the Objects Bar, then choose to Create form by
    using wizard.
   The Form Wizard dialog appears, in which you are prompted to enter a Table or
    Query, so select the Problem Log table. Then select all the fields and go to the next
   You now have the option of how to structure the fields on he form. Select Columnar
    for this form. Go to the next step.
   Here you can choose the style. Pick one that is appealing to you.
   You can leave the title as is, or change it if you want. Then click Finish.
   At this point you can use this form to enter data. Try entering a few more problems.
    Note that validation rules and referential integrity are enforced in the form.
   At the bottom of the form, you can scroll back and forth through the records in the
    Problem Log table. When you reach the end you are at an empty record that you can
    add a problem to.

Step 10: Building an initial, simple Actions Form

Repeat Step 9, this time for the Actions Table
Step 11: Refining the Problem Log Form

The form is nice, but it is inconvenient to type in data that should be selectable from a
menu. This is particularly true for fields whose values are constrained, such as the
problem category and problem status fields. Even the Assigned Employee field is difficult
to work with, because you have to enter an employee number, where it would be much
more convenient to select an employee from a menu of available employees. You will
now refine the form to get around these inconveniences. To do this, from the Forms tab in
the Database window, click on the Design toolbar button (note the blue triangular shape).
You will now be able to edit the Problem Log form.

Note that the form is comprised of many controls. These were automatically generated
with the Forms Wizard. Specifically, each field of the database has an associated label
(giving its name) and textbox (in which you can enter data. These are only two of the
many kinds of controls available to you when creating forms. To see all the types of
controls, look at the ToolBox. (If you don’t see the ToolBox, click on the ToolBox button
from the ToolBar...this is the button with a hammer and wrench.)

The ToolBox shows that you can add combo-boxes, listboxes, command buttons,
pictures, graphs, and even ActiveX objects. At this stage in the exercise, we will create
combo-boxes for the Problem Category and Problem Status fields, and a listbox for the
Assigned Employee field.
   Delete the textbox for the Problem Category field. (Its label will also be deleted).
   In the toolBox, click on the ComboBox control. Then click in the form where you
    want to place the ComboBox.
   You will be probably be presented with a ComboBox Wizard. In this case, select the
    following answers for the wizard’s questions:
    1. I will type in the values that I want.
    2. Keep the number of columns as one, and enter the legitimate values for Problem
        Category (hardware, software, data, and network)
    3. Store the value in the Problem Category field
    4. The label for this control should be Problem Category
   Otherwise (if you are not using the ComboBox Wizard) you need to perform the
    following steps:
    1. For that comboBox, you want to establish where the data comes from, and what
        are the possible values. To do this, you must get to the properties of that
        ComboBox. You can do this from the View menu or from the ToolBar (button
        with a little hand with a finger pointing down), or from the pop-up menu that
        appears by right-clicking on the control.
    2. Once in the properties, the key properties to deal with are Control Source, Row
        Source Type, and Row Source.
    3. Control Source establishes which field will be associated with that ComboBox.
        Click on the right of it, you will get an Expression Builder. From here, choose the
        Problem Log table from the left column, which will provide a list of fields from
        that table to choose from. Select Problem Category field.
    4. Row Source Type indicates how the values are to be determined. Select Value
    5. Row Source allows you to identify the allowable values. These should be typed in
        and separated with a semicolon. So, type hardware;software;network;data.
Now, you have created a ComboBox control for the Problem Category field. Similarly,
create one for the Problem Status field.

Once you have done this, save the form and then open it to see how it works. Try
scanning through the records to see what displays in the ComboBoxes.

After verifying that it works correctly, return to the Form Design view. You will now
create a listbox that enables you to assign the employee to the problem by selecting from
an alphabetized list of employees, rather than having to enter an employee number. Also,
when you are viewing the form of an already existing problem, you will see the
employee’s name highlighted.

   Delete the textbox for the Assigned Employee field. (the label will also be deleted).
   In the toolBox, click on the listbox control. Then click in the form where you want to
    place the listbox.
 The Listbox Wizard will guide you in setting up these properties. Here is what you
    should do for each step:
        1. Select Look up values in Table or Query. In this case you want the choices to
            come from the list of actual employees stored in your database.
        2. Pick the Query that you had created in Step 3...the alphabetized employee list.
        3. Select first name, then last name, then employee ID
        4. Adjust the column widths to your discretion
        5. Specify that the Employee ID is what will get stored.
        6. Specify that the field receiving the value will be the Assigned Employee field
        7. Set the label for this listbox control to be Assigned Employee
 Note: if you do not get the wizard, again you need to get into the properties of the
    control. The properties to work with include the following:
        1. Control Source should be the assigned employee field
        2. The row source type should be Table/Query
        3. The row source should be the Query alphabetized employee list (note: this will
            make the employees appear lastname, firstname, ID. Don’t worry about that.
        4. column count should be 3
        5. the bound column should be column 3
After you have done this, save the form and open it to see how it runs. You should see
that the correct employee is highlighted for each problem log record. Also, when creating
a new problem log, or modifying an existing one, you now simply select the employee
from a list rather than typing in the employee ID.
Step 12: Embedding the Action Form as a Subform of the Problem Log form

Up to this time, we have been able to successfully link the problem to the employee
within the problem log form through the listbox control. Unfortunately, there is still no
mechanism for associating all the actions of a problem log entry via the problem log
form. In this step you will accomplish this task by embedding the Action Form (created in
step 10) as a subform of the Problem Log Form.

   Go to design mode for the Problem Log Form
   From the ToolBox, select the Subform/Subreport control.
   Place the control in the desired location of your Problem Log Form
   The Subform Wizard should appear. If so, do the following:
         1. Select Use and existing form and choose the Actions form
         2. choose from a list and show the actions for each record in the problem log
            using the problem ID
         3. Keep the name as Actions
   If the wizard did not appear, go to the properties of the new Subform control and do
    the following
         1. In the Source Object property, select Actions (it’s a combobox)
       2. Note that the link child fields and link master fields properties have now been
           set to reflect the matching fields of the one-to-many relationship between
           Problem Log and Actions tables
   You may want to arrange the controls on your form so that it is entirely visible
    without the need to scroll up and down. This is easily done by selecting a control and
    dragging it to the desired location.

You are now ready to try the form again. Now, within this form, the subform allows you
to scan through all the actions associated with the particular problem log entry. Also, you
can add a new action to that problem by appending to the end of the actions within the
problem log. Isn’t that neat?

Step 13: Allowing Delete from within the Problem Log and Action Forms.

Although it is easy to add and modify problem log entries and action records, there is still
no mechanism for deleting a record from within the Problem Form or the Actions form.
To do this, we will make use of command buttons. This is very easy to do. You should
do this for both the Actions Form and the Problem Log Form.
   Get into the form design.
   Select the Command Button control from the ToolBox
   The command Button Wizard will guide you. Simply select Record Operation, Delete
    record, and decide on which picture you want (trash can, toilet, etc.)

Now, the delete operation can be triggered from within the form. Note that referential
integrity will still be enforced, provided that you specified for it when setting up your

Step 14: Building a Report

Reports are built in association with some table or query. Using the Report Wizard, see if
you can create a report that keeps track of all the work being done by each employee. See
how you can sort and group the information so that it would be most meaningful to a

Step 15: Creating a Main Menu Form

Now that you have multiple options (reports vs. forms, for example), you can create a
form that represents your main menu. Simply create an empty form, and create buttons
for at least the following functions. One button should bring up the problem log form.
Another should preview the report. Another should do a print of the report. You should
also have a button to end the application. All these are easily generated through the aid of
the Command Button Wizard. NOTE: this type of form is often called a switchboard.
Step 16: Creating a Macro to Allow You to Run the Application From Windows

In the Database Window, select the Macro Tab and press New. You will create a one-line
macro. In this macro, simply select the OpenForm action. Give it the Main Menu form as
the From Name argument.

When you save the macro, call it AutoExec. This is a special name that tells the macro to
run as soon as you call up the database. Try that. Exit Access, then double-click on your
Problem Log database. You should see the menu form appear immediately.

Step 17: Play with It

What else can you do? Experiment a little.


Hand delivery to my office – keep the zipped file on your flash.

S. Kala