As you have progressed through MIS 204, you have added several skills to your educational “tool belt” that will help you analyze data. In today’s business environment, analytical skills are in high demand because companies have large volumes of data to work with. In the past, such skills were limited to the finance and accounting departments within organizations. However, departments such as marketing, sales, production, logistics, and virtually every corner of a modern business is looking to analyze large data sets.
In this scenario, you will act in the role of analyst working for a company that helps people find adequate investments. You are charged with creating a Microsoft Access Database that will utilize the database skills you developed in Hands-On Activities 8, 10, and 11 to develop a system that analyzes businesses and helps identify opportunities for prospective investments. In fact, you have already worked with the file you will be using earlier this semester.
In Hands-On Activity 3 you used a text file called Stocks. You will use that file again for this assignment.
You will find references throughout this assessment to the original Homework Assignment Activity in the event you need to review the original material.
Build a Microsoft Access Database
Start Microsoft Access and create a new database. You should title the database LastName_FirstInitial_Final.
Import the data from the Stocks text file into a new table in your database (Originally covered in HOA 8, Populating the Tables with Data). Do not create the table. Allow Microsoft Access to create the table when you import the data. When importing, do not add a primary key. As a reminder, this file has 5,890 rows of data that contain information for publicly traded companies. This table must be named Stocks.
Create a table to track industry specialists. The table should be named Specialists and contain the following information. Create the following fields in the table:
First Name (Text), Last Name (Text), Street (Text), Zip Code (Text),
Phone (Text), Industry Expertise (Text), Email (Hyperlink)
Next, you will create a form named Specialists (Originally covered in HOA 10, Step 3). The form should be in columnar layout for ease of data entry. Then use the form to enter the list of specialists listed below.
Create a query that includes drug companies on the New York Stock Exchange that pay dividends. In addition to the company and exchange code fields, this query should also include the ticker symbol, stock price, industry name, and dividends (Originally covered in HOA 8). Name this query Drug Analysis. (Note: You should have 11 results.)
Create a query that will return the companies paying dividends with a stock price over $50. This query should contain the following fields: Company Name, Ticker Symbol, Industry Name, Stock Price, and Dividends. (Hint: You will select companies in which the dividends are greater than $0 in your query.) Name this query Dividend Opportunities.
Next, you will create a report for the companies paying dividends using the Report Wizard based on the Dividend Opportunities query. The report should include all of the fields from the original query, be grouped by Industry Name, sorted by Company Name, and use a Stepped layout. Named the report Dividend Opportunities.
After the report is initially created, modify the title of the report to ‘Companies with Dividends’. Change the Industry Name header and Industry Name field background to Text 2, Lighter 80%. The whole industry header should be a single color after changing both background properties. (Note: If this exact color is not available, then use any light blue color available to you for your background color.)
In the report body, be sure to change the Back Color and Alternate Back Color options in the Property Sheet. Change the Industry Name font size to 14.
Change the font of the page header fields to bold. There should be no background color in the page header. (Originally covered in HOA 10, Step 5).
Note: If any fields contain a series of number signs (#########), then the field is not wide enough to display its data contents and must be widened slightly.