MAN 4322 Spring 2018

MS Access Project Instructions

Please note the MS Access only works on Windows machines. You will need access to a machine with

Microsoft Office installed and will use both Microsoft Excel and Microsoft Access.

Helpful video to get you started on how to import Excel data into MS Access. https://www.youtube.com/watch?time_continue=67&v=-aH56dxqLhg

1. Download the three files provided in Canvas.

Ethnic_info.xls, personal_data.xls and phone_info.xls

2. Create a new MS Access Database. Name the database “PID_msproject” where “PID” is your

PantherID. If your PID is 1234567 then the database name will be “1234567_msproject”.

3. Import the three files into MS Access and name the tables the same names as the file names.

Validate that each emplid key is of type “Short Text”.

You do that by right clicking on the table and selecting “Design View

Then checking on the Data Type as shown below.

4. Create the following queries:

a. Query_Ethnic_Info ( Join personal_data with ethnic_info – Inner Join)

b. Query_Phone_Info ( Join personal_data with phone_info – Inner Join)

c. Query_All_EE_Phone_Info ( Join personal_data with phone_info – Outer Join)

Useful video on how to do an inner join and outer join.

At this point you show have the following in your database:

5. Export Queries to Excel.

Questions to answer How to create a Pivot Table

1. How many tables did you import?

2. How many records for each table? (i.e. Table name and number of rows)

3. How many rows for Query_ethnic_Info?

4. How many rows for Query_phone_Info?

5. How many rows for Query_All_EE_Phone_Info?

6. Using the Query_Ethnic_Info excel spreadsheet create a Pivot Table with Sex as a Row and

Count of Emplid as Sigma Values to answer the following:

a. How many Females?

b. How many Males?

c. How many Unspecified?

7. Using the Query_Ethnic_Info excel spreadsheet create a Pivot Table with City as a Row, Sex as

Columns and Count of Emplid as Sigma Values to answer the following:

a. How many Employees live in Miami?

b. How many are female?

c. How many are male?

8. Using the Query_Phone_Info excel spreadsheet create a Pivot Table with Phone_Type as a Row

and Count of Emplid as Sigma Values to answer the following:

a. What are the top three Phone Types?

b. How many Home numbers are there?

c. What is the second largest City for Phone_Type that equal HOME?

i. (Hint: Need to use Filters)

d. How many Home numbers do we have for that City?

9. Using the Query_All_EE_Phone_Info excel spreadsheet create a Pivot Table with a Filter using

only Area_Code (305, 786 and 954) with Sex as a Row, Phone_Type as a Column and answer the

following:

a. How many Home numbers are there for this group of individuals?

b. How many Females have Cell numbers?

c. How many Males have Home numbers?