APPLICATION PROBLEM SET 3

Problem 3.1

Creating an Access Database Using an Excel File

Each worksheet in the Company.xls file represents a different table in a simple company database. You are to use the data in the four worksheets in the Company.xls file to implement a database using Microsoft Access. Each worksheet has predefined column headings that are to be used to identify the fields in your database. A hard copy representation of the tables and their respective fields follows:

Employee Table:

Last Name, First Name, SSN, Department Number, Hire Date, Hourly Rate, Phone Number, Manager SSN;

Department Table:

Department Name, Manager SSN, Department Number;

Project Table:

Project Name, Project Number, Department Number;

Hour Allocation Table:

Project Hours, Project Number, SSN;

Begin the assignment by creating a database named Company in Access. Step-by-step instructions for doing this follow:

1) Find and open Access 2010 (typically, this will be under the Microsoft Office folder in the All Programs menu).

2) On the Getting Started With Microsoft Access window, click the Blank Database button. This will open a Blank Database navigation pane on the right side of the window.

3) Enter Company as the database name in the textbox. You will have to overwrite the default name (something like "Database1.accdb) to do this. Make sure that you have identified the storage location before creating the database.

4) Click Create. This will open the database for viewing, with the default table named Table 1.

You could implement the database in a number of different ways. Copying and pasting the data into self-made tables would work, as would constructing the whole thing from the ground up and keying the data manually. However, the fastest way to build the database is to import the data from Excel into Access using the import wizard under the External Data tab. Although the wizard is fairly intuitive and easy to use, I have included the following link which leads to a tutorial on the Microsoft website in case you need help (note that you have to click the Next link in the upper (or lower) right hand side of the page each time the narrator stops talking).

http://office.microsoft.com/training/training.aspx?AssetID=RC102722321033

Be advised that you will have to run the import wizard once for each of the four worksheets. Generally, the process is as follows:

1) Identify the Excel file to use; Select OK

2) Specify which worksheet in the file is to be imported; Select Next

3) Select the box to acknowledge that the first row contains column headings; Select Next

4) Bypass the option to identify data types within the Wizard by selecting Next

5) Be sure to select option to specify which field is to be used as the primary key; the primary key field for each table is as follows:

Employees SSN

Department Department Number

Project Project Number

Hour Allocation The primary key for this table should include both Project Number and SSN, but the Wizard will not allow you to specify two fields; this problem will be resolved later, after the fields have been imported. For now, select the "No primary key" option when prompted.

6) Select Finish, then select Close

Note that each table is already completely defined, and that the data have been imported into the respective tables. To finish creating the database, delete Table 1 by closing it (click on the x at the far right of the table name).

Select the Employee table by clicking on the name in the navigation pane on the left side of the screen. Then, click on the File tab on the ribbon (or on the Office button in earlier versions) to open the menu options. Finally, click File Save Database As, and select "Access 2010 Database" as the file type. This will open a dialog box that will verify that you have saved the database to your desired location, and will give you the option to rename it, if desired. Accept Company as the database name by clicking "Save".

Before you go further, you will have to identify the two primary key fields in the Hour Allocation table. To do this, open the Hour Allocation table in the Datasheet View by double clicking on the table name in the navigation pane. Then, switch to the Design View by clicking on the View button on the upper left hand side of the ribbon. To identify the two fields as primary keys, you will need to select both fields -- Project Number and SSN. Note that this is accomplished in a similar way as selecting entire rows in Excel. Move the mouse pointer over the square that is to the left of the Project Number field name until a black arrow appears, then click on the square to select the field. While you are holding down the Ctrl key, maneuver the mouse pointer over the square to the left of the SSN field until the black arrow appears, then click the square to select the field. Now, release the Ctrl key, and click on the Primary Key button at the top left portion of the ribbon. A small gold key should appear next to the field names. (Note that the Undo button can be used to revert to the previous condition, if needed.) Save your changes by clicking on the "Save" button (the floppy disk icon) at the upper left hand portion of the screen. Then, click on the x at the far right of the table name to close the Hour Allocation table.

While Access will usually try to establish informal links between database tables, it is good practice when developing databases to define the relationships between the various tables explicitly so that they conform to your design expectations. Doing so will also allow you to enforce referential integrity, which means that once they are established, the relationships between tables will be made to remain consistent (it makes it less likely that you would be able to delete a field in one table if it is the foreign key linking to another table; it will also prevent you from creating orphaned records -- deleting a record in one table if that record has dependent records that exist in another table.) The following video will show you how to do this:

http://www.youtube.com/watch?v=MhfNQci_VzU

For your database, you will want to define the following relationships. In every case, you will want to enforce Referential Integrity, and select Cascade Update and Cascade Delete (dont worry about the join type.)

Relate Departments to Employees using the Department Number field that they have in common

Relate Employees to Hour Allocation using the SSN field

Relate Projects to Hour Allocation using the Project Number field.

Note that you did not have to link Department to Projects. Thats because it generally is considered bad practice to create circular references in database design (it can make it difficult to add data in some cases, and can also lead to processing problems). It also isnt necessary: as long as some link exists between the tables, even an indirect link that requires several steps across tables like this one, you will be able to tie those tables together when you create queries and reports.

The finished Relationships should look something like this:

After you have established the relationships, save the results by clicking on the x at the right side of the Relationships tab, and clicking Yes when you are prompted to save.

Next, you are going to create a simple query using the Query Wizard. The results of your query will show all of the fields in the Employee Table. The following tutorial will give you an idea as to how to do this:

http://www.lynda.com/home/Player.aspx?lpk4=85636&playChapter=False

(An alternative video can be found here: http://www.youtube.com/watch?v=S095_Fi17sI)

Note that the video shows you how to add selected fields to the query. Since you are going to have all of the fields in the Employee table in your query, you would simply add every field (use the double arrow). After you complete the Wizard, save the query as Employee Records.

Next, you are going to create a multi-table query (a join) that shows the Department Name, the Project Name, and the employee First Name, Last Name, and Phone Number, where the employee works 20 or more hours on the given project. You will use the Query Design tool to do this.

1) Click on the Database Tools tab, then select the Query Design tool in the upper right side of the ribbon. The Query Design pane opens, with the Show Table dialog box in view. The Show Table dialog box allows you to select which tables will be used in your query. You will need the Department, Project, andEmployee tables for this query.

2) Select the Department table by double clicking on it. Note that it now appears on the field behind the dialog box. Double click on the Project and Employee tables, as well. Close the Show Table dialog box. Note that there are lines connecting the three tables by their respective primary and foreign keys (you may have to drag the tables around the field to see this clearly). These show the relationships between the tables.

(You will notice that Access has established an informal relationship between Departments and Projects; this shouldnt be a problem as it only exists for the purpose of generating certain types of queries and reports.)

3) Click on the Department Name field in the Department table. Drag the field into the row labeled "Field:" in the cells at the bottom of the Query Design window, as shown below:

Note that the Table field is automatically entered.

3) Now, drag the Project Name field into the cell next to Department Name. The Project table is also identified under the Project Name field.

4) Drag the First Name, Last Name, and Phone Number fields into position.

5) You will need to include one more field in order to limit the query to people who have worked 20 or more hours. Project hours are contained in the Hour Allocation table. To include the table, click on the Show Table button under the Query Setup group in the middle of the ribbon (under the Design tab). Double click on the Hour Allocation table in the Show Table dialog box to include with the other tables, then close the dialog box.

6) Drag the Project Hours field so that it appears next to the Phone Number field in the design grid.

7) To limit the query output to only show employees who have worked more than 20 hours, you will have to enter criteria in the grid. Note that there is a row labeled "Criteria". In the Criteria field under Project Hours, enter the limiting term >20 (do not place the term in quotation marks).

8) Suppose that you want to sort the output so that the employees' last names are listed in descending order, click on the "Sort" field under Last Name in the design grid; an arrow for a drop-down menu appears. Click on that arrow, and select "Descending" from the options.

9) Note that the Project Hours were not to be displayed in the query's output. To do that, deselect the check box under the Show field for Project Hours.

The finished grid should look like this:

10) To run the query, click on the "Run" button (the big red exclamation point) in the upper left side of the ribbon.

11) Save the query as Major Contributors by clicking on the "Save" button. Enter the query name in the Save As dialog box, and then click Okay.

Note that the two queries are listed in the navigation pane at the left side of the window.

For the last step, you are going to create a simple report using the Report Wizard. The report is to list the First Name, Last Name, and Phone Number fields in the Employee table; you are also to include the Department Name field from the Departments table. The following video will show you how to do this:

http://www.youtube.com/watch?v=DRD0_yTg5bo

As you work through the Report Wizard, specify that you want to view by Employees. Sort the output in descending order by Last Name, then by First Name (also in descending order). Have the output arranged in Tabular order, and set the page orientation to Landscape. Then, finish the report. Save the report as Phone List. Close the Phone List report by clicking on the x at the far right of the report name. Note that the Phone List report is also listed among the objects in the navigation pane.

You are now done with this project. Save the completed database by clicking File Save Database As, and select "Access 2010 Database" as the file type. Close Access.

Submit the Company database using the assignment drop-box in Canvas. If you are unable to do so, then email the file to your instructor.