MGS 3040 MIS ACCESS Assignment – Stock Track Database Instructions This project builds upon the Excel assignment and should be done in the…

MGS 3040 MIS
ACCESS Assignment – Stock Track Database
Instructions
This project builds upon the Excel assignment and should be done in the same team. The objective of this project is to create a relational database to keep track of company information and monthly stock prices.

For this assignment each group will need to enter profile and historical information for the five stocks they had selected in project one into an Access database. You will have to generate some queries, and reports.

The completion of this assignment requires that you use Access. In order to learn and practice Access you can use the materials from your CPS 1032 class or one of the many tutorials on the web.

Step 1 – Create the Company table
Create an Access table to store the company information you gathered in Project 1- step 1. Do not forget to designate the appropriate primary key. Your table should look like the one presented in Figure 1.

Figure 1. Company Table
Ticker
Exchange
Company Name
Address
City
State
Zip code
Phone
Sub-Sector
Employees

(a) Load the data into your company table (you can try to import the Word table you created in Project one, or enter the data manually in datasheet view).

(b) Create a data entry form for this table and use it to add the information for 3 more companies. Use any 3 new tech stocks.

Step 2 – Create the Monthly Stock Price table
Using Yahoo! financial find the monthly stock prices of your five companies and the three new ones that you added, for the past six months. Create another Access table similar to the one shown in Figure 2.

What should the primary key be? Which field (or combination or fields) are unique for each record? (hint: to create a key with more that 1 field in Access, go in the design view of the table, highlight all the fields you want in the key, and click in the little key on the menu bar)

Figure 2. Monthly Stock Prices
Ticker
Date
Open
High
Low
Close
Volume

To populate this table you can import the historical price information retrieved in a spreadsheet, or you can create a form and enter this information manually.

As you create these tables in Access, set the appropriate field types and sizes. Field sizes that are unnecessarily long for the data that will be stored in them reflect poor design. Assume that each of your tables may grow to several hundred records and you should avoid wasting a lot of storage space.

Step 3 – Relationship
Create a relationship between the two tables. Be sure to enforce referential integrity.

Step 4 – Queries
Run the following queries: Save each one on your disk with appropriate and meaningful names.

(a) Display in alphabetical order the name and the corresponding ticker symbol and state of the companies that are not headquartered in California.

(b) Display the ticker, name and number of employees of the companies with more than 5,000 employees.

(c) Display the names of all the companies located in California and whose closing stock price was higher than $15.00 in any given month (Show only company name, state, date and closing price, do not show the ticker symbol).

Step 5 – Reports
1. Use the Report Wizard to create the following reports. Save each one.

(a) A columnar report containing an alphabetical list of companies and their corresponding sub-sectors, showing company name, sub-sector, complete address and telephone number.

(b) A report containing a list of companies with their Average closing stock price, and Maximum and Minimum price over the period covered sorted by ticker symbol.

(c) A tabular/stepped report containing a list of companies matched with their monthly stock prices, grouped by month and sorted by company name. Show only the company name, the month and the open and close stock price for the month. Do not include the ticker symbol in this report.

Submit for grading
Each team should submit a database file with the following 10 items (2 table contents, 1 form, 1 relationship, 3 queries and 3 reports), and share the file with me on Skydrive Live or Google Docs (do NOT convert the database into Google format). Your database should be complete – that is, the items should show everything necessary to answer the questions and to grade your assignment.

Note: Submit your project by midnight on the due date. Late submissions will not be accepted.