skip to Main Content
The smarter way
to do assignments.

Please note that this is just a preview of a school assignment posted on our website by one of our clients. If you need assistance with this question too, please click on the Order button at the bottom of the page to get started.

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:

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 (don’t
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. That’s 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 isn’t 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:

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 shouldn’t 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:

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.

GET HELP WITH THIS ASSIGNMENT TODAY

Clicking on this button will take you to our custom assignment page. Here you can fill out all the additional details for this particular paper (grading rubric, academic style, number of sources etc), after which your paper will get assigned to a course-specific writer. If you have any issues/concerns, please don’t hesitate to contact our live support team or email us right away.

How It Works        |        About Us       |       Contact Us

© 2018 | Intelli Essays Homework Service®