Access Project

MS ACCESS PROJECT

The MS Access project is group oriented to allow individuals to exchange ideas and learn from each other. You will build an Access database to store stock, student, and investment information with data downloaded from the Internet and copied from other sources.

Each group member starts with $10,000 in imaginary money which will be invested after the closing of the market on Friday 2/18/05, and will be sold at the closing bell on Friday 3/11/05. Each group member must invest all the group money in 2 different S&P 100 stocks. You cannot invest less than $1,000 per stock. The group that has earned the most money (or lost the least) at the end of the period will receive an extra 5 points for each group member.

Group Composition

Each group will consist of 4 to 5 members of same responsibilities that will equally share the workload. You will create your own groups in class. Each member of the group will be assigned the same grade unless otherwise shown in peer-evaluation. When it comes to peer-evaluation, the total group points will be divided among the members in your group, in which case you will be assigned a grade that will reflect your evaluation. This grade can be higher or lower than the group grade.

Table Definitions and Table Data

* Build a new database named MIS175G000.MDB, where 000 is your assigned group number.

* Import all data from SP100Stocks worksheet in the SP100Stocks.xls Excel workbook into a new table in your database. The new table will be named Stocks. The primary key for this table is the Symbol field. After the Stocks table has been created, change the size of the Symbol field to 6 Text characters, the Stock Name field to 40 characters, and the EPS (Earnings Per Share) field to a currency with 3 decimal places.

* Build a new table named Groups with the following specifications:

Field Name Data Type Key

GroupNumber Text (4) Yes

GroupName Text (50)

GroupMeetings Memo

* Create a new record in the Groups table with your assigned group number, a group name of your choice and, in the GroupMeetings field, type the days and hours you have decided to meet.

* Build a new table named Members with the following specifications:

Field Name Data Type Key

GroupNumber Text (4)

MemberID AutoNumber Yes

FirstName Text(15)

LastName Text(18)

EmailAddress Hyperlink

Picture OLE Object

Major Text (5)

PhoneNumber Text

* You must define a mask for the Phone Number field. In other words, Phone Number field should have the appropriate input mask. The Major field only accepts the following values: MIS, AIS, ACCT, FIN, GMGT, MKTG, OTHER. GroupNumber displays the values from the GroupNumber field of the Groups table (lookup). It should be mandatory to type data to all the fileds of the Members table (no blank fields).

* Build a new table named Trades with the following specifications:

Field Name Data Type Key

TradeNumber AutoNumber Yes

MemberID Number

Symbol Text (6)

TradeDate Date/Time

AmountInvested Currency

* TradeDate field described above is formatted as short date. Default value is 2/18/05.

* Build a new table named Prices with the following specifications:

Field Name Data Type Key

Symbol Text (6) Yes

Date Date/Time Yes

Price Currency

* Date field described above are formatted as short date.

* Each Friday, starting on 2/18/05 until 3/11/05, you must record the closing price for each of your stocks. Enter your closing prices in the Prices table only for the stocks purchased by the group. Note: You can monitor and record each closing price every week, or download the historical data once on the last week (will show you how to find past closing prices in class). You must also record the purchase prices in this table. Note that the prices of stocks for 2/18/05 should be recorded before the form in Fig 3 can be completed.

* Create the Relationships (ER Diagram) for the five tables created up to this point, as shown below (Fig. 1).

Fig. 1

Form Definitions

* Create one BMP file with the picture of each team member. Each picture should be less than 100 KB in size. You may use any method to create the graphic files with your pictures (i.e., scanning a paper picture, digital camera, photo collage). You will use this picture in the Picture