Problem Set

Part A: MS Access - Creating Tables

In ELM J (MS Access) slide, there are four tables, such as Employee, Job, Department, and Job Assignment. Make the same four tables with the same data using MS Access program.

You need to include your personal data in the table, i.e., your last four digits of social security number and your last name as a sixth data in the Employee table. Your department is "Commercial" and your job is "Acct".

You are working "8" hours in a "Job Assignment" column. Print four separate tables in the "Datasheet View" (not a "Design View") and submit them (20 points).

Part B: MS Access - Creating Queries and Reports

Click "Queries" menu in MS Access, and click "Create Queries in Design view". Use all of four tables in the query and show the following four fields in the query;

Department Name, Employee Name, Job Assignment Hours, and Job Name.

Make criteria, which is more than 4 (>4) in Job Assignment Hours column. And run this query and save it as a query table. In MS Access, click "Create" and "Report by Wizard". Use all of four fields in a query table to make a report.

You can use any report type to show these results, which is Department Name, Employee Name, Job Assignment Hours, and Job Name information, who has more than 4 hours of job assignment. Print the report (not query table) and submit them.

Part C: Decision Support and MS Excel

Download "xlmd_customer.xls" (MS Excel Example) file from the D2L. Open the file and do the following tasks, and submit the result outputs.

(a) Create a pivot table that shows "Rent vs Own" (horizontal) by "Region" (vertical). In the central data part of pivot table, show the count (number) of "Customers" and sum of "Num Purchases".

(b) Use Custom AutoFilter to show the original data worksheet (not pivot table) only with those customers who have six "Num Household" with more than 9 or less than 2 "Num Purchases" (not including 9 and 2).

Part D: Net Present Value (NPV) function in MS Excel

Using the NPV worksheet in the "xlmd_customer.xls", calculate the following NPVs using Excel financial function and submit the separate printout of this worksheet.

Show the different NPVs in the printout using each of five interest rates (5%, 6%, 7%, 8%, 9%) when you have $17,000 as initial cost of investment one year from today and return from first year ($5,000), second year ($5,000), third year ($5,000) and fourth year ($5,000).

Attachment:- Data_customer.rar